Free up disk space 1

[barbola]

Our databases have grown alot in the last year and there is no more room to add cards, so we need a new controller card (this is what I am told). Apparently this costs alot of money, which is why I think it is being delayed.

Currently we only have enough space to keep 3 days of backups. This is unacceptable as far as I am concerned as well as company policy (5 days is required) but nobody seems to be making it a priority.

We run daily database backups (kept 5 days) and hourly transaction logs (kept 3 days). We run weekly update stats and weekly data integrity check. These are our four maintenance plans.

One of my ideas was to move some 10 or so old databases that are no longer used to CD. But my concern about this is, when we upgrade to the new Accounting system (Great Plains) then we need to find a way to upgrade these old db's in case we need to access them, and I'm not 100% sure how to do this without paying hundreds of dollars for advice from our vendor. This is why I think we need to upgrade the server, because the db's we have are only getting larger and larger anyways.

Are there any utilities or SQL statements I can run in the meantime to free up some space? I ran dbcc shrinkdatabase on one of the old db's as a test. It decreased the db file from 220 to 166, but the log file increased from 78 to 122 MB.

Any other suggestions are appreciated.

 

[SQLBill]

When I need to reclaim unused space, I don't run DBCC SHRINKDATABASE. I run DBCC SHRINKFILE and shrink the data and log files separately.

Do you keep ALL of your backups on disk? Why?

I do my backups to disk and then copy the backup files to tape. That way I can keep overwriting the disk backup file.

-SQLBill

Posting advice: FAQ481-4875

 

[barbola]

What is the proper way to use DBCC SHRINKFILE in query analyzer? It gave me an error when I typed that with the proper DB selected.

Server: Msg 2583, Level 16, State 3, Line 1
An incorrect number of parameters was given to the DBCC statement.

They are kept on the same drive as the data on SQL Server. Dumb, I know. I'm not the tech person, but I think they do also back up the SQL server onto our main network or some tape drives.

I will check with them, but I think it's because the tech guys never really found a foolproof way to accomplish this, and once they tried it and I needed a backup, and they didn't know how to get it for me.

 

[SQLBill]

Look up the syntax in the BOL. Basically it's:

DBCC SHRINKFILE (filename)

To find the proper file name, open Enterprise Manager, find your database, right click on it, select Properties and go to the Data tab and then Log tab. Those will show the actual file name for each of the files. That's the name you need to use. If there are spaces in the file name, enclose it in square brackets.

DBCC SHRINKFILE ([mydb primary data file])

-SQLBill

Posting advice: FAQ481-4875

 

[barbola]

Thanks Bill!

Now, can you also please help me come up with a good reason why our Tech guys should be copying the BACKUP files to the off-site server and not the DATA files!!! grrrrrrr

I try to explain to them that is why we use the backup feature, that's why it's there! You can restore to a point in time. It's not the preferred NOR recommended method to re-attach a db.

I was told they backup any changed files in the data directory.

thanks.

 

[SQLDenis]

sp_helpdb DataBaseName will also give back the filename for example

sp_helpdb 'pubs'



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[barbola]

DBCC SHRINKDATABASE (XXXXXXXXDat.mdf)

gives me:

ServerLine 1: Incorrect syntax near '.'.
: Msg 170, Level 15, State 1, Line 1

and

DBCC SHRINKDATABASE (XXXXXXXXDat)

gives me:

Server: Msg 2520, Level 16, State 12, Line 1
Could not find database 'GPSHYPRODat'. Check sysdatabases.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Do I have to be on the server to perform this? ohhhh yes probably eh?

 

[barbola]

...and maybe I should have typed shrinkfile and not shrinkdatabase ....

can you tell it's Monday for me???

 

[barbola]

Well this sux. I'm on the server now, and tried the same as above:

DBCC SHRINKFILE (databasenameDat.mdf)

and

DBCC SHRINKFILE (databasenameDat)

and also tried

DBCC SHRINKFILE ([full path and filename])

and get the same error.

 

[SQLDenis]

file_name is the logical name of the file
when you run sp_helpdb 'pubs'
the name column will have the logical file name so for pubs it will be

DBCC SHRINKFILE (pubs)
DBCC SHRINKFILE (pubs_log)




Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[barbola]

Yes, I used the exact name it showed as the name. Tried it with and without the path and with and without the extension too.

name
GPSxxxxxDat.mdf

filename
d:\MSSQL$SQL1\Data\GPSxxxxxDat.mdf

(xxxxx is my company name)

DBCC SHRINKFILE (GPSxxxxxDat.mdf) tells me error with the dot.

 

[barbola]

...and when i put the square brackets around it, it works.

did you see my other post there about the backups?

 

[SQLDenis]

DBCC SHRINKFILE (GPSxxxxxDat)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[barbola]

alrighty. It worked, but....

Now, my database file decreased in size, but my log file is HUGE! and it won't shrink!

What do I do now? I hate mondays.

 

[SQLDenis]

backup log LogName with truncate_only

then shrinkfile

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLBill]

a good reason why our Tech guys should be copying the BACKUP files to the off-site server and not the DATA files!!!

Because it won't work.

If the database is online, then the .mdf and .ldf files CANNOT be copied. Well, okay, they can be copied but they are useless and cannot be attached or anything else. To copy the .mdf/.ldf files, the database needs to be offline (detached or SQL Server services stopped).

Plus, you need to get the WHOLE file......copying just what has changed doesn't work. These are not your normal Windows files.

If they want to copy the .mdf/.ldf files, tell them they need to justify (to management) taking the database offline. If they tell you that's not necessary, make them prove it will work by restoring the files to another server (or another instance on the same server). It won't work.

-SQLBill

Posting advice: FAQ481-4875

 

[barbola]

I asked them to clarify "data" files, and they meant to say "data" drive. The programs are on one drive, and the data and backups and transaction logs are on another drive. I'm not sure how they know what has changed though, and I'll tell them they need to backup ALL the backups.

I asked them how easy I could restore from one of those backups, and they said I would need training in Veritas?

I think I need to go to a higher up. It shouldn't be the tech guys' decision to prioritize what is backed up, but only to ensure data IS backed up and IS retrievable.

 

[SQLBill]

My opinion:

Personnel responsible for making backups should ALWAYS sit down with the people who are responsible for the data. They should find out how the data 'works' and how it has to be backed up. For most things, just making a copy of the files is good enough. But with SQL Server, you can't just make a copy of the .mdf/.ldf files.

-SQLBill

Posting advice: FAQ481-4875

 

[barbola]

Yep we've sat down with them many times, and they are the type who think they know what they're doing so they don't really 'listen'.

 

[ecobb]

It sounds to me like they are just using Veritas (BackupExec) to make a backup of the files on the hard drive and they have no knowledge SQL Server backups and how they work. They don't understand what you're asking, and they *think* you don't understand what you're asking. As SQLBill said, you can't just make a copy of the .mdf/.ldf files to a tape and then restore from that. You'll need to get them to make copies of the .bak and .trn files with BackupExec.

Most likely, if they're backing up the drive that has the backups (.bak&.trn) on it then you should be ok. In the event something happens just tell them that you need a copy of last night's files from the backup, then you can take it and restore through SQL Server.


Hope This Helps!

ECAR
ECAR Technologies

http://www.ecartech.com

"My work is a game, a very serious game." - M.C. Escher