Database Size problems

I have a database that has grown much larger than I expect. There is a
specific table that seems to be unusally large. If I copy the data from that
table out into a temp table then copy it back in, it frees up over 90% of the
space that it was taking up.
I cannot alter the database structure at this point so is there a better way
to free this space up ? I have tried things like shrinkdb and shrinkfile and
whilst they create a little space its not anywhere near the level of moving
the data.
Does anyone have any ideas ?
SiSimon
How large is your database? Do you have limited space on the disk?
1) You can put the large table on different physical disk array
2) You use partition to "divide" the table
BTW , what is the version are you using?
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:A58E5518-5D24-4FBC-AE57-C50D822F75BB@.microsoft.com...
>I have a database that has grown much larger than I expect. There is a
> specific table that seems to be unusally large. If I copy the data from
> that
> table out into a temp table then copy it back in, it frees up over 90% of
> the
> space that it was taking up.
> I cannot alter the database structure at this point so is there a better
> way
> to free this space up ? I have tried things like shrinkdb and shrinkfile
> and
> whilst they create a little space its not anywhere near the level of
> moving
> the data.
> Does anyone have any ideas ?
> Si|||How many indexes do you have on that table? How often do you rebuild
the indexes?
It looks like your table had a lot of fragmentation, which as a result
made t use a lot more space than necessary.
Markus|||The database is around 40Gig
But one table is 35G of that. When I copy the data out and then back in it
becomes around 3G.
I`m using SQL2000.
I know I can split the data onto seperate disks but I was hoping to write a
job that will shrink the data down automatically.
"Uri Dimant" wrote:
> Simon
> How large is your database? Do you have limited space on the disk?
> 1) You can put the large table on different physical disk array
> 2) You use partition to "divide" the table
> BTW , what is the version are you using?
>
> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> news:A58E5518-5D24-4FBC-AE57-C50D822F75BB@.microsoft.com...
> >I have a database that has grown much larger than I expect. There is a
> > specific table that seems to be unusally large. If I copy the data from
> > that
> > table out into a temp table then copy it back in, it frees up over 90% of
> > the
> > space that it was taking up.
> > I cannot alter the database structure at this point so is there a better
> > way
> > to free this space up ? I have tried things like shrinkdb and shrinkfile
> > and
> > whilst they create a little space its not anywhere near the level of
> > moving
> > the data.
> >
> > Does anyone have any ideas ?
> >
> > Si
>
>|||Simon wrote:
> The database is around 40Gig
> But one table is 35G of that. When I copy the data out and then back in it
> becomes around 3G.
> I`m using SQL2000.
> I know I can split the data onto seperate disks but I was hoping to write a
> job that will shrink the data down automatically.
>
As Markus said, this sounds like a fragmentation problem. Use DBCC
SHOWCONTIG to check the fragmentation of the indexes on that table, and
have a look at this script of mine:
http://realsqlguy.com/twiki/bin/view/RealSQLGuy/DefragIndexesAsNeeded
Tracy McKibben
MCDBA
http://www.realsqlguy.comsql

Database setup for backup and recovery

I am new to SQLServer but a DB2 DBA.

I want to be able to backup a specific table and restore it. Actually, i may want to backup/restore several tables - a sub-set of tables on the database.

I understand that backup-recovery is at a database level, not a table-file-filrgroup level. Therefore i have to backup-restore a database, but i only want a table.

How do sites handle this. Are many databases created based on backup recovery requirements.

If so, then how do developers know what database tables reside in - given that there are now many databases created to handle recovery requirements. A synonymns/alias/views added ?

tia

glenn

If you put the table on its own filegroup, you can restore just that filegroup/table. Else, you will need to restore the database to a staging db and copy/transfer the table from the staging db to the real db.|||

Can i restore a single filegroup to a previous point in time (say 8am), but keep the rest of the database unchanged (say 10am).

Or, does the whole database have to be at the same point in time after a restore?

tia

Database setup for backup and recovery

I am new to SQLServer but a DB2 DBA.

I want to be able to backup a specific table and restore it. Actually, i may want to backup/restore several tables - a sub-set of tables on the database.

I understand that backup-recovery is at a database level, not a table-file-filrgroup level. Therefore i have to backup-restore a database, but i only want a table.

How do sites handle this. Are many databases created based on backup recovery requirements.

If so, then how do developers know what database tables reside in - given that there are now many databases created to handle recovery requirements. A synonymns/alias/views added ?

tia

glenn

If you put the table on its own filegroup, you can restore just that filegroup/table. Else, you will need to restore the database to a staging db and copy/transfer the table from the staging db to the real db.|||

Can i restore a single filegroup to a previous point in time (say 8am), but keep the rest of the database unchanged (say 10am).

Or, does the whole database have to be at the same point in time after a restore?

tia

Database Security

Hi experts, I would like to ask if it is feasible to limit the accessibility of an SA account in SQL 2005 in a specific database. The reason of doing this procedure is since we are deploying a package software to our client(s) we want to secure our own database to get tampered by our client(s).

No its not possible to restrict SA from any database. There are many post on this topic on this forum

check this

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1993336&SiteID=1

Madhu

|||Is there any suggestion on how could we secure our Database? for a possible tampering? or changing the data types.|||

Create DDL trigger on this database and prevent tampering or log tampering of the db objects. Its very good option avaliable in sql server 2005. Generally, you should remove Built/AdminGroup,Guest from the database. Set strong password for SA

Madhu

|||Thanks to your effort. I will try this for now|||

check my blog for some DDL script

http://madhuottapalam.blogspot.com/search?q=ddl+trigger

Madhu|||

I just want to emphasize that (as Madhu mentioned) it is not possible to restrict members of sysadmin from any database. Using triggers and other mechanisms to try to avoid tampering can be very helpful for keeping honest people honest and to prevent modifying the schema by mistake, but a sysadmin with enough determination won’t be stopped by such mechanisms.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

Database Security

Hi experts, I would like to ask if it is feasible to limit the accessibility of an SA account in SQL 2005 in a specific database. The reason of doing this procedure is since we are deploying a package software to our client(s) we want to secure our own database to get tampered by our client(s).

No its not possible to restrict SA from any database. There are many post on this topic on this forum

check this

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1993336&SiteID=1

Madhu

|||Is there any suggestion on how could we secure our Database? for a possible tampering? or changing the data types.|||

Create DDL trigger on this database and prevent tampering or log tampering of the db objects. Its very good option avaliable in sql server 2005. Generally, you should remove Built/AdminGroup,Guest from the database. Set strong password for SA

Madhu

|||Thanks to your effort. I will try this for now|||

check my blog for some DDL script

http://madhuottapalam.blogspot.com/search?q=ddl+trigger

Madhu|||

I just want to emphasize that (as Madhu mentioned) it is not possible to restrict members of sysadmin from any database. Using triggers and other mechanisms to try to avoid tampering can be very helpful for keeping honest people honest and to prevent modifying the schema by mistake, but a sysadmin with enough determination won’t be stopped by such mechanisms.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

Database restore speed

This problem isn't specific to SQL Server, but because of the size of the files I deal with with SQL Server, it is the place I notice it more often, and hopefully one of you has, too.

If I am restoring a database (or simply copying a huge file) that takes more than a couple of minutes, I find, by watching the network bandwidth, that after a couple of minutes, the data rate cuts on half and stays that way for the rest of the restore (or copy). If I have a Gb/s connection, maybe I start at 240Mb/s and then drop off to 120MB/s. If I have a 100Mb/s connection, maybe I start at 70Mb/s and drop to 30-40MB/s. It is fairly consistant in how long before the drop and in the magnatude of the drop (approximately 50%).

The network staff have no clue. They say that they have put nothing in place to throttle bandwidth hogs. It doesn't seem to matter which servers the transfer is going between. It doesn't matter if they are plugged into the same switch or go across several switches.

I have googled for any reference to this with no luck. Has anyone else experienced this? Does anyone know the cause?

A couple of possibilities:

Have you observed Perfmon data on the drive you are copying your large file to? If the Average Disk Queue Length is exceeding 2 on a sustaining basis you are saturating the Disk I/O

Check the Read/Write cache ratio on your disk controller(s), if they are set to 100% Read and you are trying to write a large file to disk, it's going to slow it down significantly

Do you have the /3GB switch enabled in the boot.ini file on your server? We found on a number of our servers that large file copies (22GB+) were actually failing because we were exhausiting the Kernel resources on both the Source and Target servers. The resolution in this particular case was to remove the /3GB switch from both the source and target servers and then the large file copies succeeded.

Huge memory paging (2,000 pages/sec +) may also be another area to look into

Check the network card on your source and target servers to make sure they are negiotiating at Full Duplex. I have found that the network folks set the ports on the switch to Auto/Auto and the server SA's set the NIC cards to Forced Full. This causes a negotiation conflict and forces the NIC to negotiate at half duplex or worse

|||One reason I don;t think it is the target drive is that with I restore a DB, before data is read from the backup, the whole target file is written. With permon, I have seen it write out a 40G file at much higher, fairly constant rate than the fastest rate it will write to once it starts loading it with data from the backup. I haven't tried it lately, but I suspect I wouldn't see this throttling behavior if the backup file is on the local machine. Maybe I will try that to confirm.|||The /3GB switch in your boot.ini would help|||

I just tried it between two Win2003x64 servers with 8G of ram each and saw the same phenomenon. Presumably, the /3G option would be moot in this case.

|||

Can't believe I found this thread by accident, when I just encountered the same error this morning

I was copying a 12GB SQL bak file from one server to another (identical servers, Windows 2003 R2 32-bit, 8GB RAM with /PAE and /3GB boot.ini, 15K rpm 200GB RAID5 disks, Gigabit NICs both at Auto speed)

The file copy would come to almost dead after a while (it initially displayed 3 minutes remaining, where NIC utilization is at 60%), then about one minute after (the estimate remaining time starts to go up to ~15 minutes, and NIC utilization is at 4~9% only

I saw this thread, remove the /3GB in boot.ini in BOTH servers, restart, re-copy, and the same thing still happened. Now I'm at a loss to what caused this

|||

We have the same problem - win2003x64(AMD) server - 1 with 4G ram the other 1G ram. Coping from the one with 4 to the 1 with 1.

Anyone ever come up with a solution for this? Would be most appreciated.

Thanks!

Mark

|||Regarding restore speed, do you have Windows Instant File Initialization enabled? You need to give the SQL Server Service account the right (in Group Policy Editor, for example), to "Perform Volume Maintenance Tasks". Otherwise, Windows has to zero out the space allocated for the file, which makes the restore take at least 3-4 times longer. You will have to restart the SQL Server Service for this to take effect.|||

GlennAlanBerry,

Does this apply to SQL Server 2000 or just 2005? I just checked my SQL service account and it has this right, but (in 2000) it still makes the empty file first. In 2005, I have seen that it starts restoring instantly (determined by seeing that it is pulling data from across the network).

Most of the time, I am restoring over an existing DB, so this doesn't make a whole lot of difference to my speed issues. When it does matter, the writing of the empty file usually goes several times faster than the actual restore. That is what concerns me. With RAID5 arrays on either end and a gigabit connection in between, you would think that the array write speed should be the bottleneck. Even before this odd throttling affect kicks in, it isn't the bottleneck. I did a series of restores last night. Based on the statistics at the end, the smaller DBs (2G and 6G) restored at around 20MB/s, but the larger ones (16G and 47G) restored at around 8MB/s. I wasn't watching the network throughput, but I'm sure if I was, I would have seen the all to familiar pattern of starting off fast and then abruptly dropping to a slower speed after a few minutes.

|||Windows Instant File Initialization is not used by SQL Server 2000

Database restore speed

This problem isn't specific to SQL Server, but because of the size of the files I deal with with SQL Server, it is the place I notice it more often, and hopefully one of you has, too.

If I am restoring a database (or simply copying a huge file) that takes more than a couple of minutes, I find, by watching the network bandwidth, that after a couple of minutes, the data rate cuts on half and stays that way for the rest of the restore (or copy). If I have a Gb/s connection, maybe I start at 240Mb/s and then drop off to 120MB/s. If I have a 100Mb/s connection, maybe I start at 70Mb/s and drop to 30-40MB/s. It is fairly consistant in how long before the drop and in the magnatude of the drop (approximately 50%).

The network staff have no clue. They say that they have put nothing in place to throttle bandwidth hogs. It doesn't seem to matter which servers the transfer is going between. It doesn't matter if they are plugged into the same switch or go across several switches.

I have googled for any reference to this with no luck. Has anyone else experienced this? Does anyone know the cause?

A couple of possibilities:

Have you observed Perfmon data on the drive you are copying your large file to? If the Average Disk Queue Length is exceeding 2 on a sustaining basis you are saturating the Disk I/O

Check the Read/Write cache ratio on your disk controller(s), if they are set to 100% Read and you are trying to write a large file to disk, it's going to slow it down significantly

Do you have the /3GB switch enabled in the boot.ini file on your server? We found on a number of our servers that large file copies (22GB+) were actually failing because we were exhausiting the Kernel resources on both the Source and Target servers. The resolution in this particular case was to remove the /3GB switch from both the source and target servers and then the large file copies succeeded.

Huge memory paging (2,000 pages/sec +) may also be another area to look into

Check the network card on your source and target servers to make sure they are negiotiating at Full Duplex. I have found that the network folks set the ports on the switch to Auto/Auto and the server SA's set the NIC cards to Forced Full. This causes a negotiation conflict and forces the NIC to negotiate at half duplex or worse

|||One reason I don;t think it is the target drive is that with I restore a DB, before data is read from the backup, the whole target file is written. With permon, I have seen it write out a 40G file at much higher, fairly constant rate than the fastest rate it will write to once it starts loading it with data from the backup. I haven't tried it lately, but I suspect I wouldn't see this throttling behavior if the backup file is on the local machine. Maybe I will try that to confirm.|||The /3GB switch in your boot.ini would help|||

I just tried it between two Win2003x64 servers with 8G of ram each and saw the same phenomenon. Presumably, the /3G option would be moot in this case.

|||

Can't believe I found this thread by accident, when I just encountered the same error this morning

I was copying a 12GB SQL bak file from one server to another (identical servers, Windows 2003 R2 32-bit, 8GB RAM with /PAE and /3GB boot.ini, 15K rpm 200GB RAID5 disks, Gigabit NICs both at Auto speed)

The file copy would come to almost dead after a while (it initially displayed 3 minutes remaining, where NIC utilization is at 60%), then about one minute after (the estimate remaining time starts to go up to ~15 minutes, and NIC utilization is at 4~9% only

I saw this thread, remove the /3GB in boot.ini in BOTH servers, restart, re-copy, and the same thing still happened. Now I'm at a loss to what caused this

|||

We have the same problem - win2003x64(AMD) server - 1 with 4G ram the other 1G ram. Coping from the one with 4 to the 1 with 1.

Anyone ever come up with a solution for this? Would be most appreciated.

Thanks!

Mark

|||Regarding restore speed, do you have Windows Instant File Initialization enabled? You need to give the SQL Server Service account the right (in Group Policy Editor, for example), to "Perform Volume Maintenance Tasks". Otherwise, Windows has to zero out the space allocated for the file, which makes the restore take at least 3-4 times longer. You will have to restart the SQL Server Service for this to take effect.|||

GlennAlanBerry,

Does this apply to SQL Server 2000 or just 2005? I just checked my SQL service account and it has this right, but (in 2000) it still makes the empty file first. In 2005, I have seen that it starts restoring instantly (determined by seeing that it is pulling data from across the network).

Most of the time, I am restoring over an existing DB, so this doesn't make a whole lot of difference to my speed issues. When it does matter, the writing of the empty file usually goes several times faster than the actual restore. That is what concerns me. With RAID5 arrays on either end and a gigabit connection in between, you would think that the array write speed should be the bottleneck. Even before this odd throttling affect kicks in, it isn't the bottleneck. I did a series of restores last night. Based on the statistics at the end, the smaller DBs (2G and 6G) restored at around 20MB/s, but the larger ones (16G and 47G) restored at around 8MB/s. I wasn't watching the network throughput, but I'm sure if I was, I would have seen the all to familiar pattern of starting off fast and then abruptly dropping to a slower speed after a few minutes.

|||Windows Instant File Initialization is not used by SQL Server 2000

Database restore speed

This problem isn't specific to SQL Server, but because of the size of the files I deal with with SQL Server, it is the place I notice it more often, and hopefully one of you has, too.

If I am restoring a database (or simply copying a huge file) that takes more than a couple of minutes, I find, by watching the network bandwidth, that after a couple of minutes, the data rate cuts on half and stays that way for the rest of the restore (or copy). If I have a Gb/s connection, maybe I start at 240Mb/s and then drop off to 120MB/s. If I have a 100Mb/s connection, maybe I start at 70Mb/s and drop to 30-40MB/s. It is fairly consistant in how long before the drop and in the magnatude of the drop (approximately 50%).

The network staff have no clue. They say that they have put nothing in place to throttle bandwidth hogs. It doesn't seem to matter which servers the transfer is going between. It doesn't matter if they are plugged into the same switch or go across several switches.

I have googled for any reference to this with no luck. Has anyone else experienced this? Does anyone know the cause?

A couple of possibilities:

Have you observed Perfmon data on the drive you are copying your large file to? If the Average Disk Queue Length is exceeding 2 on a sustaining basis you are saturating the Disk I/O

Check the Read/Write cache ratio on your disk controller(s), if they are set to 100% Read and you are trying to write a large file to disk, it's going to slow it down significantly

Do you have the /3GB switch enabled in the boot.ini file on your server? We found on a number of our servers that large file copies (22GB+) were actually failing because we were exhausiting the Kernel resources on both the Source and Target servers. The resolution in this particular case was to remove the /3GB switch from both the source and target servers and then the large file copies succeeded.

Huge memory paging (2,000 pages/sec +) may also be another area to look into

Check the network card on your source and target servers to make sure they are negiotiating at Full Duplex. I have found that the network folks set the ports on the switch to Auto/Auto and the server SA's set the NIC cards to Forced Full. This causes a negotiation conflict and forces the NIC to negotiate at half duplex or worse

|||One reason I don;t think it is the target drive is that with I restore a DB, before data is read from the backup, the whole target file is written. With permon, I have seen it write out a 40G file at much higher, fairly constant rate than the fastest rate it will write to once it starts loading it with data from the backup. I haven't tried it lately, but I suspect I wouldn't see this throttling behavior if the backup file is on the local machine. Maybe I will try that to confirm.|||The /3GB switch in your boot.ini would help|||

I just tried it between two Win2003x64 servers with 8G of ram each and saw the same phenomenon. Presumably, the /3G option would be moot in this case.

|||

Can't believe I found this thread by accident, when I just encountered the same error this morning

I was copying a 12GB SQL bak file from one server to another (identical servers, Windows 2003 R2 32-bit, 8GB RAM with /PAE and /3GB boot.ini, 15K rpm 200GB RAID5 disks, Gigabit NICs both at Auto speed)

The file copy would come to almost dead after a while (it initially displayed 3 minutes remaining, where NIC utilization is at 60%), then about one minute after (the estimate remaining time starts to go up to ~15 minutes, and NIC utilization is at 4~9% only

I saw this thread, remove the /3GB in boot.ini in BOTH servers, restart, re-copy, and the same thing still happened. Now I'm at a loss to what caused this

|||

We have the same problem - win2003x64(AMD) server - 1 with 4G ram the other 1G ram. Coping from the one with 4 to the 1 with 1.

Anyone ever come up with a solution for this? Would be most appreciated.

Thanks!

Mark

|||Regarding restore speed, do you have Windows Instant File Initialization enabled? You need to give the SQL Server Service account the right (in Group Policy Editor, for example), to "Perform Volume Maintenance Tasks". Otherwise, Windows has to zero out the space allocated for the file, which makes the restore take at least 3-4 times longer. You will have to restart the SQL Server Service for this to take effect.|||

GlennAlanBerry,

Does this apply to SQL Server 2000 or just 2005? I just checked my SQL service account and it has this right, but (in 2000) it still makes the empty file first. In 2005, I have seen that it starts restoring instantly (determined by seeing that it is pulling data from across the network).

Most of the time, I am restoring over an existing DB, so this doesn't make a whole lot of difference to my speed issues. When it does matter, the writing of the empty file usually goes several times faster than the actual restore. That is what concerns me. With RAID5 arrays on either end and a gigabit connection in between, you would think that the array write speed should be the bottleneck. Even before this odd throttling affect kicks in, it isn't the bottleneck. I did a series of restores last night. Based on the statistics at the end, the smaller DBs (2G and 6G) restored at around 20MB/s, but the larger ones (16G and 47G) restored at around 8MB/s. I wasn't watching the network throughput, but I'm sure if I was, I would have seen the all to familiar pattern of starting off fast and then abruptly dropping to a slower speed after a few minutes.

|||Windows Instant File Initialization is not used by SQL Server 2000

database Question..

hi everyone..

im using asp.net with vb.net.

i have a situation, there are two drop down list, 1)Car Model 2) Car Colours. A set of specific colours would fill up the second dropdown list based on the Car Model choosen on the first drop down list.

my question is how do i create a database table that enables me to extract the specific colours based on the car model chosen?.How to create database to accomodate this kinda specific info?

hope to get some feedback here.thank you.Hi

There are two ways to go about this and I suppose the option you choose to take will depend on how normalized you want your database to be.

OPTION 1:
----

One Entry for each individual car in the car table
Car Table
--> CarID
--> CarDescription

One entry for each combination of car/colour in the colour table.
Colour Table
--> ColourID
--> ColourDescription
--> CarID (Foreign Key)

Thus you might have 15 entries for the colour blue, but each of these entries will have a different CarID (i.e. the cars that are available in blue). When the car selected is then say the car with CarID 10, populate the colour dropdown with each colour from colour dropdown that has a CarID of 10.

OPTION 2:
----

One Entry for individual car in the car table
Car Table
--> CarID
--> CarDescription

One Entry for individual colour in the colour table
Colour Table
--> ColourID
--> ColourDescription

And then a third table that maps CarID's to ColourID's:
Car_Colour Table
--> CarID (Foreign Key)
--> ColourID (Foreign Key)

Now when CarID 10 is selected in the car dropdown, populate the colour dropdown with each colour that is mapped to CarID 10 in the Car_Colour table...

HTH|||Dear Daily...

Thanks a heap!!!...really appreicate it..:)