Scenario:
Database file size (.mdf): 250 MB
Log file size (.ldf): 10 GIGABYTES
Quick solution
Make a transaction log backup of your database. Shrink the log file. Make a new transaction log backup.
See BOL for the How to on your version of the SQL Server.
Reason
Many administrators mistakenly think that using the full recovery model (which is default) and then making a daily full backup is sufficient maintenance.
The problem is that the full backup does not flush the inactive transactions from the transaction log when the full backup finishes - this is the case both for SQL 2000 and SQL 2005.
There are different ways to solve the problem - the right one in my opinion being regularly to back up the log file - which flushes the log - and then solving the problem in the long run.
Long term solution
Schedule at least one daily transaction log backup - I would recommend in the middle of the work-day if only 1. Eg. Full backup at 23:00, T-Log at 12:00. If the database has any important activity during the day (and most does) then you properbly want to take transaction log backup every 2 or 3 or 4 hours during the day. Worst case - you can go back to the time of the last t-log backup. If you only take one full backup a day - worst case you need to go 24 hours back......
Friday, July 13, 2007
Help my log file is growing huge !
How many files in a filegroup
Because SQL Server opens one IO thread for each file pr. filegroup - it gives the server a higher read/write performance to have more than one file pr. filegroup.
This does not mean though, that you should have as many files as possible! The optimal amount of files pr. filegroup is between 0.25 and 1 pr. CPU. Meaning that if your Server has 4 CPUs you should have between 1 and 4 files pr. filegroup.
Please note that when I write CPUs I acutally mean physical CPU Cores !
My experience is that you normally would benefit the most by aligning the number of files with the number of CPUs. Eg. a server with 1 CPU with 2 physical cores should have 2 files pr. filegroup in general.
This is always true for TEMPDB where you should have 1 file pr. CPU.
Wednesday, July 11, 2007
Performance tuning counters for SQL Server
As an instructor I always tell my students to make a performance baseline on their SQL Server so that they have something to compare against, when (not if) the users complain about performance. But my students and customers often ask – which counters should I monitor then? Well here is, to my experience, a good list to start with. Arguably you could narrow or widen the list – but for a start this list has in my experience proven to be sufficient. Performance object Counter Warning Alert Notes Memory Pages/sec 100 200 I know that many sites say 20 – but that was from the NT4 days with 128 MB RAM Memory Available MBytes 200 100 SQL Server: Buffer Manager Buffer Cache Hit Ratio 90 85 SQL Server: Buffer Manager Page Life Expectancy 350 300 These should be per physical disk Physical Disk Disk Reads/sec No levels – just monitor these Physical Disk Disk Writes/sec No levels – just monitor these Physical Disk % Disk time No levels – just monitor these Physical Disk % Disk time No levels – just monitor these Physical Disk Avg. Disk Queue Length 1,5 2 This is x times the number of spindles in the raid set. E.g. Raid 1 with 2 disk = 4 Processor % Processor Time 70 80 System Processor Queue Length Network Interface Bytes Received/sec No levels – just monitor these Network Interface Bytes Sent/sec No levels – just monitor these Network Interface Output Queue Length No levels – just monitor these SQL Server: General User Connections No levels – just monitor these SQL Server: Memory Manager Connection Memory (KB) No levels – just monitor these SQL Server: Memory Manager Total Server Memory (KB) No levels – just monitor these You don't have to monitor the counters all the time to create a good baseline, the warning and alert levels are included for those of you who want to implement the counters in your normal performance monitoring system like MOM.
Move a database between servers - WITH the logins
Quite a few times I have been asked if moving a database from one server to another would be any problem.
Generally this is not a problem - although you can save yourself some trouble by moving the logins for the database first.
The How-To is:
1) Script the logins for the database on the source server by using the scripts listed below (Originally posted by Microsoft)
Remember to filter the logins the script generates - it takes every login on the source server and you generally only want those with reference to the database being moved.
2) After filtering the logins from the source server apply the needed logins on the destination server before attaching the database.
3) Detach the database on the source server, move it, attach it on the destination server
The detach/attach procedure can most easily be done from Management Studio by right-clicking the database name in the object explorer -> Select Task -> Select Detach. On the destination server right click the folder Databases -> Select Attach.
The scripts that script the logins
The scripts is originally posted by Microsoft. They come in two variants - one for SQL 2000 and one for SQL 2005.
Both scripts consist of the same. 2 stored procedures - sp_hexadecimal, and sp_help_revlogin. sp_help_revlogin genereates a new script in the result windows with one line for each login on the server. Copy the text result from the result window, and filter it before applying it on the destination server.
