Microsoft SQL Server
From Jonsdocswiki
Microsoft SQL Server is an implementation of the SQL database engine from Microsoft, nice and obvious! Speaking MSSQL is a slightly different dialect to MySQL but the fundaments are the same.
Contents |
Query Analyser
Query analyser allows you to run queries on tables in the SQL database. All queries run through here are logged in the transaction log and can be rolled back if necessary.
SQL Enterprise Manager
You can act on the database using the SQL Enterprise Manager. This tool allows you to setup backup jobs, perform maintenance on the databases and run queries. If you're only running queries though, you should use Query Analyser - this prevents accidentally making mistakes and damaging your database.
The taskpad view can be very helpful as it clearly shows database and transaction log usage as well as allow you to quickly perform a number of actions on the database.
SQL EM Taskpad error
When working in enterprise manager and using the taskpad view there are script errors that occur and throw an error to the user. Unhelpfully, this then stops the taskpad view from working.
Fix
To resolve this change the view for the selected database to something else (for example small icons) and then change it back to Taskpad afterward.
RAM Usage
By default, stupidly but this is Microsoft, SQL server is told to use a very high amount of memory - up to 2GB on one of the servers I found. This will cause system RAM to reach a critically low level as the sqlservr.exe process takes all the RAM (1.5GB has been reported as not unusual).
Configuring RAM usage
- Open SQL Server Enterprise Manager (or equivalent)
- Expand Microsoft SQL Servers and any server groups until the server you want is visible.
- Right click the desired server and click Properties
- SQL Server Properties (Configure) appears, select the Memory tab
- Adjust the Maximum (MB): slider to the maximum desired amount of RAM the SQL server can use
- Click OK
You will need to restart the SQL server for the changes to take affect.
See also
Transaction logs
MS SQL keeps a transaction log - all queries are found in here and can be rolled back if necessary. The transaction log fills up and when it reaches full will prevent any further database transactions taking place - it is important to make sure the freespace in the transaction log is kept at a good size.
Truncating the transaction log
| Warranty and Responsibility |
|
Please note, Jonathan Haddock and this wiki's maintainers can accept no liability or responsibility for any problems that arise as a result of following these instructions.
|
- Open Enterprise Console
- Right click the database in question
- Click All Tasks then Backup Database...
- A window will appear SQL Server Backup, select Transaction log (ensure there is a dot in the circle)
- Set a valid Destination
- Switch to the Options tab
- Ensure the box Remove inactive entries from transaction log is ticked
- Click OK
This will backup the transaction log and remove unneeded entries now.
Alternate instructions
Alternate instuctions from Ifor Gaukroger, friend of Jonathan's:
Rather than truncating it is best to backup the log to reduce the usage of the transaction log file itself with
backup log <db-name> to DISK = 's:\<name-of-backup-file>'
This would then allow you to make the transaction log file smaller if that was required with
-- Run select * from sysfiles or look in db properties to find out the logical name
dbcc shrinkfile(<logical-name-of-transaction-log-file>, <size-in-mb-to-attempt-to-reduce-file-down-to>)
IF (big if!), however you really don't mind breaking the chain of log backups that would allow a point in time restore then you can always run
BACKUP log <db-name> with TRUNCATE_ONLY
which will just throw away the data in the transaction log. If you do this and do want to maintain or start proper transaction logs then do an immediate database backup.
Some links re transaction logs
- http://msdn.microsoft.com/en-us/library/ms186865.aspx
- http://www.devx.com/dbzone/Article/39194/1954
- http://www.sqlskills.com/blogs/paul/post/Search-Engine-QA-1-Running-out-of-transaction-log-space.aspx
- http://support.microsoft.com/kb/873235
