We have a table that is showing a large amount of "reserved" space:
Rows: 4095513
Reserved 11255328 KB
Data: 1176296 KB
Index_size: 531240 KB
unused: 9547792 KB
(found wiht sp_spaceused)
We have only simple types in this table... no ntext or blobs or
anything.
Just int, smalldatetime, varchar(50) etc. (only 10 fields)
So our data and indices come to about 1.6 GB, but "unused" space is 9.1
GB.
We are running out of drive space...why would it reserve so much space
for this table?"cmay" <cmay@.walshgroup.com> wrote in message
news:1139343553.343130.288690@.g43g2000cwa.googlegroups.com...
> We have a table that is showing a large amount of "reserved" space:
> Rows: 4095513
> Reserved 11255328 KB
> Data: 1176296 KB
> Index_size: 531240 KB
> unused: 9547792 KB
> (found wiht sp_spaceused)
> We have only simple types in this table... no ntext or blobs or
> anything.
> Just int, smalldatetime, varchar(50) etc. (only 10 fields)
> So our data and indices come to about 1.6 GB, but "unused" space is 9.1
> GB.
> We are running out of drive space...why would it reserve so much space
> for this table?
>
What did you specify when you created the database? Take a look at DBCC
SHRINKFILE.
Rick Sawtell
MCT, MCSD, MCDBA|||Rick,
The DB was created like 10 years ago, so I have no idea what happened
back then.
The DB is set to auto grow by 10%. From what I have read shrinking the
DB won't solve this problem. The DB is 15 GB with just about all of it
being considered "used" (I could get back a couple hundred MB by
shrinking the DB), but the 9GB of space being eaten up by this one
table is what is really painful.
I tried running DBCC CleanTable but that didn't help either.
I'm going to try to insert all the data into a new table and then drop
the old one, but that isn't really ideal.
If there are any other things I can try I would love to give it a go.|||Can you try rebuilding the clustered index? That will automatically move all
the data to new space.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"cmay" <cmay@.walshgroup.com> wrote in message
news:1139354564.333651.258690@.g14g2000cwa.googlegroups.com...
> Rick,
> The DB was created like 10 years ago, so I have no idea what happened
> back then.
> The DB is set to auto grow by 10%. From what I have read shrinking the
> DB won't solve this problem. The DB is 15 GB with just about all of it
> being considered "used" (I could get back a couple hundred MB by
> shrinking the DB), but the 9GB of space being eaten up by this one
> table is what is really painful.
> I tried running DBCC CleanTable but that didn't help either.
> I'm going to try to insert all the data into a new table and then drop
> the old one, but that isn't really ideal.
> If there are any other things I can try I would love to give it a go.
>|||We actually didn't have a clustered index on this table.
I ended up running an insert from the offending table to a new table,
then dropped the old one, and the new one looks great.|||In general all tables should have a clustered index. You have no way to
control fragmentation or issues like this without one.
--
Andrew J. Kelly SQL MVP
"cmay" <cmay@.walshgroup.com> wrote in message
news:1140532518.546041.33250@.z14g2000cwz.googlegroups.com...
> We actually didn't have a clustered index on this table.
> I ended up running an insert from the offending table to a new table,
> then dropped the old one, and the new one looks great.
>
Friday, March 30, 2012
Recipient name becomes <unknown sender>
Our SQL Server was moved to a new domain. A stored procedure that emails ou
t
with sender's address as [@.v_from = 'abc@.abc.com'] suddenly appears in
Outlook w/o the sender name in "From" column like it used to. In its place
now is <unknown sender>.
Please help.
Gracias
LaEsmeraldaLaEsmeralda wrote:
> Our SQL Server was moved to a new domain. A stored procedure that emails
out
> with sender's address as [@.v_from = 'abc@.abc.com'] suddenly appears in
> Outlook w/o the sender name in "From" column like it used to. In its pla
ce
> now is <unknown sender>.
> Please help.
> Gracias
> LaEsmeralda
What name would you usually see in the from field? It sounds like it's
more of an Outlook problem than it's a SQL server problem. When you send
out emails from Outlook, it will use your Exchange server to resolve the
name into what ever name is in the Global Addresse Book. SInce you've
moved your SQL server to a new domain, it could be that Outlook isn't
able to resolve the name. What happens if you open Outlook on the SQL
server (with the profile that are used for sending out emails) and then
send an email from there. Will you then get the correct name in the
"from" field?
Regards
Steen|||Outlook works normally.
"Steen Persson (DK)" wrote:
> LaEsmeralda wrote:
> What name would you usually see in the from field? It sounds like it's
> more of an Outlook problem than it's a SQL server problem. When you send
> out emails from Outlook, it will use your Exchange server to resolve the
> name into what ever name is in the Global Addresse Book. SInce you've
> moved your SQL server to a new domain, it could be that Outlook isn't
> able to resolve the name. What happens if you open Outlook on the SQL
> server (with the profile that are used for sending out emails) and then
> send an email from there. Will you then get the correct name in the
> "from" field?
> Regards
> Steen
>
t
with sender's address as [@.v_from = 'abc@.abc.com'] suddenly appears in
Outlook w/o the sender name in "From" column like it used to. In its place
now is <unknown sender>.
Please help.
Gracias
LaEsmeraldaLaEsmeralda wrote:
> Our SQL Server was moved to a new domain. A stored procedure that emails
out
> with sender's address as [@.v_from = 'abc@.abc.com'] suddenly appears in
> Outlook w/o the sender name in "From" column like it used to. In its pla
ce
> now is <unknown sender>.
> Please help.
> Gracias
> LaEsmeralda
What name would you usually see in the from field? It sounds like it's
more of an Outlook problem than it's a SQL server problem. When you send
out emails from Outlook, it will use your Exchange server to resolve the
name into what ever name is in the Global Addresse Book. SInce you've
moved your SQL server to a new domain, it could be that Outlook isn't
able to resolve the name. What happens if you open Outlook on the SQL
server (with the profile that are used for sending out emails) and then
send an email from there. Will you then get the correct name in the
"from" field?
Regards
Steen|||Outlook works normally.
"Steen Persson (DK)" wrote:
> LaEsmeralda wrote:
> What name would you usually see in the from field? It sounds like it's
> more of an Outlook problem than it's a SQL server problem. When you send
> out emails from Outlook, it will use your Exchange server to resolve the
> name into what ever name is in the Global Addresse Book. SInce you've
> moved your SQL server to a new domain, it could be that Outlook isn't
> able to resolve the name. What happens if you open Outlook on the SQL
> server (with the profile that are used for sending out emails) and then
> send an email from there. Will you then get the correct name in the
> "from" field?
> Regards
> Steen
>
Recipient name becomes <unknown sender>
Our SQL Server was moved to a new domain. A stored procedure that emails out
with sender's address as [@.v_from = 'abc@.abc.com'] suddenly appears in
Outlook w/o the sender name in "From" column like it used to. In its place
now is <unknown sender>.
Please help.
Gracias
LaEsmeraldaLaEsmeralda wrote:
> Our SQL Server was moved to a new domain. A stored procedure that emails out
> with sender's address as [@.v_from = 'abc@.abc.com'] suddenly appears in
> Outlook w/o the sender name in "From" column like it used to. In its place
> now is <unknown sender>.
> Please help.
> Gracias
> LaEsmeralda
What name would you usually see in the from field? It sounds like it's
more of an Outlook problem than it's a SQL server problem. When you send
out emails from Outlook, it will use your Exchange server to resolve the
name into what ever name is in the Global Addresse Book. SInce you've
moved your SQL server to a new domain, it could be that Outlook isn't
able to resolve the name. What happens if you open Outlook on the SQL
server (with the profile that are used for sending out emails) and then
send an email from there. Will you then get the correct name in the
"from" field?
Regards
Steen|||Outlook works normally.
"Steen Persson (DK)" wrote:
> LaEsmeralda wrote:
> > Our SQL Server was moved to a new domain. A stored procedure that emails out
> > with sender's address as [@.v_from = 'abc@.abc.com'] suddenly appears in
> > Outlook w/o the sender name in "From" column like it used to. In its place
> > now is <unknown sender>.
> > Please help.
> > Gracias
> > LaEsmeralda
> What name would you usually see in the from field? It sounds like it's
> more of an Outlook problem than it's a SQL server problem. When you send
> out emails from Outlook, it will use your Exchange server to resolve the
> name into what ever name is in the Global Addresse Book. SInce you've
> moved your SQL server to a new domain, it could be that Outlook isn't
> able to resolve the name. What happens if you open Outlook on the SQL
> server (with the profile that are used for sending out emails) and then
> send an email from there. Will you then get the correct name in the
> "from" field?
> Regards
> Steen
>
with sender's address as [@.v_from = 'abc@.abc.com'] suddenly appears in
Outlook w/o the sender name in "From" column like it used to. In its place
now is <unknown sender>.
Please help.
Gracias
LaEsmeraldaLaEsmeralda wrote:
> Our SQL Server was moved to a new domain. A stored procedure that emails out
> with sender's address as [@.v_from = 'abc@.abc.com'] suddenly appears in
> Outlook w/o the sender name in "From" column like it used to. In its place
> now is <unknown sender>.
> Please help.
> Gracias
> LaEsmeralda
What name would you usually see in the from field? It sounds like it's
more of an Outlook problem than it's a SQL server problem. When you send
out emails from Outlook, it will use your Exchange server to resolve the
name into what ever name is in the Global Addresse Book. SInce you've
moved your SQL server to a new domain, it could be that Outlook isn't
able to resolve the name. What happens if you open Outlook on the SQL
server (with the profile that are used for sending out emails) and then
send an email from there. Will you then get the correct name in the
"from" field?
Regards
Steen|||Outlook works normally.
"Steen Persson (DK)" wrote:
> LaEsmeralda wrote:
> > Our SQL Server was moved to a new domain. A stored procedure that emails out
> > with sender's address as [@.v_from = 'abc@.abc.com'] suddenly appears in
> > Outlook w/o the sender name in "From" column like it used to. In its place
> > now is <unknown sender>.
> > Please help.
> > Gracias
> > LaEsmeralda
> What name would you usually see in the from field? It sounds like it's
> more of an Outlook problem than it's a SQL server problem. When you send
> out emails from Outlook, it will use your Exchange server to resolve the
> name into what ever name is in the Global Addresse Book. SInce you've
> moved your SQL server to a new domain, it could be that Outlook isn't
> able to resolve the name. What happens if you open Outlook on the SQL
> server (with the profile that are used for sending out emails) and then
> send an email from there. Will you then get the correct name in the
> "from" field?
> Regards
> Steen
>
Recipe for a good, solid maintenance plan
Hello
I'm in the proces of a major revision of the maintenance plans for the SQL servers in our company, and in connection with that I would like to hear how other people are doing this.
Here's a quick rundown of the plan:
Critical DBs will be backed up on tape (daily incremental, weekly full, w. Veritas Backup Exec 9.0). Also, there will be full daily disk backups for easy quick recovery. These will be done locally, as I have bad experiences trying to backup across a network share.
DBs of medium importance will be backed up fully every day on disk. These BAK-files will then be backed up on tape, if I find it necessary.
Although I rarely restore from the tapes, I think they're nice to have in case the office burns down or who knows what.
The maintenance plans will be split into 3:
1. System DBs maint. plan
2. Critical importance DBs maint. plan
3. Medium importance DBs maint. plan
The more I think about it, the more I think I might just classify all production DBs as critical and all test DBs as medium. Maybe that would make more sense.
For all disk backups, optimization and integrity checks (and backup) will be done daily. For DBs of critical importance (eg. production DBs) Transaction log back will be done as well. What's a good schedule for this? Once every 3 hours or so? Every hour? How much burden does this operation put on the server?
I guess that's about it so far. If anyone has any suggestions or comments, I would be very pleased to hear them.
MNJFrequency of trx. log backups depends on the level of activity of action queries and recoverability requirements. In one of our databases here we're doing 15-minute trx. log dumps and the resulting file varies from 800MB to 2.5GB in size. Another database barely creates a 100K logs but we're doing dumps every 30 minutes for its point-in-time recoverability requirements. It all depends.
As per your breakdown, it looks good. But as our disaster recovery excersises shown, - it's beneficial to have your system databases backed up last. Here we're using SQLMAINT utility to run our maintenance plans (SQLMAINT -PlanName <app_db_maint_plan>). This way it's easier to sequence the steps to your likes. Also, do make sure you log all outputs, in case something goes south :)|||[i]As per your breakdown, it looks good. But as our disaster recovery excersises shown, - it's beneficial to have your system databases backed up last. Here we're using SQLMAINT utility to run our maintenance plans (SQLMAINT -PlanName <app_db_maint_plan>). This way it's easier to sequence the steps to your likes. Also, do make sure you log all outputs, in case something goes south :)
Why is it beneficial to backup the system DBs last? Since I keep system and user DBs separated into different maint. plans, I guess I can just schedule the system maint. plan to occur 15 min. after the user main. plan?
MNJ|||For one, if MSDB is backed up last, - it will contain the latest backup information of all other databases, as well as itself. This information is available when looking at the General tab of database Properties window.
As per scheduling, - as I said earlier, I have execution of all maintenance plans in one batch with SQLMAINT. If you're using Scheduled Tasks, then you can add a step with SQLMAINT -PlanName <sys_db_maint_plan> after your application databases.|||Good point with system DBs, I will take that into consideration. I suppose if I just make sure to schedule them a bit apart, it should work out ok.
MNJ
I'm in the proces of a major revision of the maintenance plans for the SQL servers in our company, and in connection with that I would like to hear how other people are doing this.
Here's a quick rundown of the plan:
Critical DBs will be backed up on tape (daily incremental, weekly full, w. Veritas Backup Exec 9.0). Also, there will be full daily disk backups for easy quick recovery. These will be done locally, as I have bad experiences trying to backup across a network share.
DBs of medium importance will be backed up fully every day on disk. These BAK-files will then be backed up on tape, if I find it necessary.
Although I rarely restore from the tapes, I think they're nice to have in case the office burns down or who knows what.
The maintenance plans will be split into 3:
1. System DBs maint. plan
2. Critical importance DBs maint. plan
3. Medium importance DBs maint. plan
The more I think about it, the more I think I might just classify all production DBs as critical and all test DBs as medium. Maybe that would make more sense.
For all disk backups, optimization and integrity checks (and backup) will be done daily. For DBs of critical importance (eg. production DBs) Transaction log back will be done as well. What's a good schedule for this? Once every 3 hours or so? Every hour? How much burden does this operation put on the server?
I guess that's about it so far. If anyone has any suggestions or comments, I would be very pleased to hear them.
MNJFrequency of trx. log backups depends on the level of activity of action queries and recoverability requirements. In one of our databases here we're doing 15-minute trx. log dumps and the resulting file varies from 800MB to 2.5GB in size. Another database barely creates a 100K logs but we're doing dumps every 30 minutes for its point-in-time recoverability requirements. It all depends.
As per your breakdown, it looks good. But as our disaster recovery excersises shown, - it's beneficial to have your system databases backed up last. Here we're using SQLMAINT utility to run our maintenance plans (SQLMAINT -PlanName <app_db_maint_plan>). This way it's easier to sequence the steps to your likes. Also, do make sure you log all outputs, in case something goes south :)|||[i]As per your breakdown, it looks good. But as our disaster recovery excersises shown, - it's beneficial to have your system databases backed up last. Here we're using SQLMAINT utility to run our maintenance plans (SQLMAINT -PlanName <app_db_maint_plan>). This way it's easier to sequence the steps to your likes. Also, do make sure you log all outputs, in case something goes south :)
Why is it beneficial to backup the system DBs last? Since I keep system and user DBs separated into different maint. plans, I guess I can just schedule the system maint. plan to occur 15 min. after the user main. plan?
MNJ|||For one, if MSDB is backed up last, - it will contain the latest backup information of all other databases, as well as itself. This information is available when looking at the General tab of database Properties window.
As per scheduling, - as I said earlier, I have execution of all maintenance plans in one batch with SQLMAINT. If you're using Scheduled Tasks, then you can add a step with SQLMAINT -PlanName <sys_db_maint_plan> after your application databases.|||Good point with system DBs, I will take that into consideration. I suppose if I just make sure to schedule them a bit apart, it should work out ok.
MNJ
Recieving E-Mail by Service broker
I'm beginner...
I need recieve e-mail by Service broker.
What can I do it?
(example if possible).
Thanks!
Service Broker provides reliable messaging in and between databases and does not interface with other systems directly (such as e-mail). You could, however, write a Service Broker service that receives e-mail from IMAP, POP3 or any other e-mail protocol and send them to another Service Broker service. Use Books Online to get started on how to use the technology.
Rushi
|||Thanks!
In some book I read about this.
Probably it isn't true.
|||i'm with you it's true but faile to make it if you found any solve tell me pleaserecieve error in vdi.log when backup SQL 2000 Server DB
using arcserve 2000 to backup database receive error in
VDI.log:
2006/10/05 02:03:24 pid(1948) tid(500)
Error on DBASQL70TID500VD0
Error at TriggerAbort: invoked
2006/10/05 02:03:43 pid(1312) tid(1892)
Error on DBASQL70TID500VD0
Error at SVDS::Open: Dup(stateEvent) Status Code: 6, x6
Explanation: The handle is invalid.
2006/10/05 02:03:45 pid(1948) tid(500)
Error on DBASQL70TID500VD0
Error at TriggerAbort: invoked
2006/10/05 02:03:46 pid(1948) tid(500)
Error on DBASQL70TID500VD0
Error at TriggerAbort: StateEvent Status Code: 6, x6
Explanation: The handle is invalid.
2006/10/05 02:03:46 pid(1948) tid(500)
Error on DBASQL70TID500VD0
Error at TriggerAbort: CompletionSem Status Code: 6, x6
Explanation: The handle is invalid.
2006/10/05 02:03:46 pid(1948) tid(500)
Error on DBASQL70TID500VD0
Error at CVDS::Cleanup: Close(State) Status Code: 6, x6
Explanation: The handle is invalid.
2006/10/05 02:03:47 pid(1948) tid(500)
Error on DBASQL70TID500VD0
Error at CVDS::Cleanup: Close(CompleteSem) Status Code: 6, x6
Explanation: The handle is invalid.
MS SQL Server version is 2000.
Pls help. Thanks
Don't have an answer but check out similar post @. http://forums.databasejournal.com/showthread.php?t=40177sql
VDI.log:
2006/10/05 02:03:24 pid(1948) tid(500)
Error on DBASQL70TID500VD0
Error at TriggerAbort: invoked
2006/10/05 02:03:43 pid(1312) tid(1892)
Error on DBASQL70TID500VD0
Error at SVDS::Open: Dup(stateEvent) Status Code: 6, x6
Explanation: The handle is invalid.
2006/10/05 02:03:45 pid(1948) tid(500)
Error on DBASQL70TID500VD0
Error at TriggerAbort: invoked
2006/10/05 02:03:46 pid(1948) tid(500)
Error on DBASQL70TID500VD0
Error at TriggerAbort: StateEvent Status Code: 6, x6
Explanation: The handle is invalid.
2006/10/05 02:03:46 pid(1948) tid(500)
Error on DBASQL70TID500VD0
Error at TriggerAbort: CompletionSem Status Code: 6, x6
Explanation: The handle is invalid.
2006/10/05 02:03:46 pid(1948) tid(500)
Error on DBASQL70TID500VD0
Error at CVDS::Cleanup: Close(State) Status Code: 6, x6
Explanation: The handle is invalid.
2006/10/05 02:03:47 pid(1948) tid(500)
Error on DBASQL70TID500VD0
Error at CVDS::Cleanup: Close(CompleteSem) Status Code: 6, x6
Explanation: The handle is invalid.
MS SQL Server version is 2000.
Pls help. Thanks
Don't have an answer but check out similar post @. http://forums.databasejournal.com/showthread.php?t=40177sql
Receving error with no description
I have designed quite a few reports over the past few weeks, and today I received the following error:
An error occured during local report processing.
The definition of the report /Discharge7.rdl is invalid.
Exception of type: 'Microsoft.ReportingServices.ReportProcessing.ReportProcessingException' was thrown.
I've never seen this error before. I've gotten errors with typos in names of fields in my expressions, but I've never gotten an error on the 40 someodd other forms I have.
Does anyone know what could be causing this?
Did u tried to preview this report before uploading it to the server, the error says that is not definned correctly, so the report must be bad, when you make a preview on the reporting services it will show you the detailed errors.
|||You can also look at the logs for more detailed error messages. These are located here on a default installation: C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\LogFiles\For more information about the various Reporting Services log files:
http://msdn2.microsoft.com/en-us/library/ms157403.aspx|||This was while I was previewing it in Visual Studio in the report designer. I haven't published it to the server yet.
Subscribe to:
Posts (Atom)