question

Deleting data from a database will (generally) not change the data file sizes. Seems like you have 2 issues: **Log file size** A log file of 7GB for data of 8GB sounds like you are not backing up your logs. What recovery model are you using? **Data file size** After deleting some data, how much of the data file is still in use? Here's a script I use to determine sizes declare @cmd varchar(500) CREATE TABLE #tmplog ( DBName varchar(100), LogSize real, LogSpaceUsed real, Status int ) create table #tmpdata ( fileid int, filegroup int, totalextents int, usedextents int, name varchar(1024), filename varchar(1024) ) set @cmd = 'dbcc sqlperf (logspace)' insert into #tmplog execute (@cmd) set @cmd = 'DBCC SHOWFILESTATS' insert into #tmpdata execute (@cmd) delete from #tmplog where DBName db_name() SELECT dbname, totalextents*64/1024 as DataSizeMB, usedextents*64/1024 as DataUsedMB, round(cast(usedextents as real)/totalextents * 100, 0) as 'Data%Used', round(logsize,0) as LogSizeMB, round(logspaceused,0) as LogUsedMB, round(logspaceused/logsize * 100, 0) as 'Log%Used' from #tmpdata cross join #tmplog --clean up drop table #tmplog drop table #tmpdata

It sounds like you have landed the role of 'accidental dba'. Hold on tight, you're in for a rough but exciting ride! I am going to guess that your database is in full recovery mode and are not running log file backups. You can check what recovery mode you are in by running the following query (replace YOURDATABASENAME with the real db name): SELECT recovery_model_desc FROM sys.databases D WHERE name = 'YOURDATABASENAME' You now need to ask yourself if your database needs to be in FULL recovery, or if SIMPLE would be enough. If this is all new to you, you need to read up on the [recovery models in sql server][1] and [when to choose which recovery model][2] If my guess is right and the database is in full recovery and it is possible to switch to simple recovery (please read and understand the links I gave above), then a possible solution would be to switch to simple recovery: ALTER DATABASE YOURDATABASENAME SET RECOVERY SIMPLE You will then want to make a full backup of the database (something that may not be happening at present and is ***REALLY REALLY IMPORTANT!***) BACKUP DATABASE YOURDATABASENAME TO DISK 'X:\YOURBACKUPFOLDER\YOURBACKUPFILE.BAK' SQL Server should then have backed up your database so you can restore it at a later date. As mentioned in the links I provided, SIMPLE recovery now no longer requires you to make log file backups, full backups are what you would be making (and you should). You can now take care of the log file which was growing beyond what you wanted. The log file should now be in a state where it will have quite a bit of space inside that it can now free up. I suggest you run the following script to clear this space and shrink the log file down to a more normal size: This will show you the filenames of your database. Note the logical name of the log file. USE YOURDATABASENAME GO exec sp_helpfile Now using the logical filename change the script below to fit your needs. Filesize should be healthily large. If your DB is 8GB, try out with 512MB to start with. DBCC SHRINKFILE(logicalfilename, TRUNCATEONLY) ; ALTER DATABASE YOURDATABASENAME MODIFY FILE ( NAME = logicalfilename , SIZE = size ) DBCC SHRINKFILE(logicalfilename, TRUNCATEONLY) ALTER DATABASE databasename MODIFY FILE ( NAME = logicalfilename , SIZE = size ) You will need to monitor the logfile size over the next few days, the same as the DB size. Both are important for the database and how well it performs. I also recommend that you read up on all aspects of this side of database management. If you need help on anything, ask here - we will help (no question is too simple/silly). [1]: http://msdn.microsoft.com/en-us/library/ms189275.aspx [2]: http://msdn.microsoft.com/en-us/library/ms175987.aspx

Hi, You can run the following script if you Database is in **Full Recovery Mode**. For my point of view, shrinking the database is not good (you might consider to buy bigger harddrive, or like Kev said to make sure regular log backup to maintain the size of the log). USE [YourDB]; GO -- Truncate the log by changing the database recovery model to SIMPLE. ALTER DATABASE [YourDB] SET RECOVERY SIMPLE; GO Checkpoint Go -- Shrink the truncated log file to 100 MB. DBCC SHRINKFILE ([YourDB], 100); GO -- Reset the database recovery model. ALTER DATABASE [YourDB] SET RECOVERY FULL; GO backup database [YourDB] to disk = 'E:\YourDB.bak' with init Go Regular Log Backup (depends on how busy of your Data is) Please see the link below for more info - [Shrink DB LOG][1] [1]: http://ask.sqlservercentral.com/questions/30835/need-to-shrink-db-log