[Database] - Tips for Maintaining your SQL Database

Discussion in 'FAQ / Guides / Мануалы' started by Fr3DBr, Jul 3, 2011.

Thread Status:
Not open for further replies.
  1. Fr3DBr

    Fr3DBr Guest

    Hello,

    I'd like to give you a few useful tips for maintaing your SQL Databases in good conditions.

    Of course some of your clients are aware of that and have a good SQL Knowledge,
    but if we can sum our knowledge together, we can have a better result ;).

    1) How to purge/clean SQL Logs ?

    a) replace databasename string with the name of your database like : lin2db or lin2world etc.
    b) turn off all programs like cached, apache(xampp,wamp) and anything that can be using your database.
    c) run this query :

    USE [databasename]
    GO
    DBCC SHRINKFILE('databasename_log', 1)
    GO

    d) after doing it, your log size should be reduced to the minimum possible ! :)

    2) How to make a backup ?

    a) Replace X: to your desired drive to make your backup.
    b) replace databasename string to the name of your database like : lin2db or lin2world etc.
    c) run this query :

    BACKUP DATABASE [databasename] TO DISK = N'X:\BACKUP\databasename.bak' WITH NOFORMAT, INIT, NAME = N'databasename-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
    GO

    d) after running it you must have a .bak file placed in the desired place you wanted, so there is your backup that you can restore any time, running this query again, will overwrite last backup with a newer copy.

    3) How to reindex (optimize) your database ?

    a) replace databasename string to the name of your database like : lin2db or lin2world etc.
    b) run this query :

    USE [databasename]
    GO
    DECLARE @TableName varchar(255)

    DECLARE TableCursor CURSOR FOR
    SELECT table_name FROM information_schema.tables
    WHERE table_type = 'base table'

    OPEN TableCursor

    FETCH NEXT FROM TableCursor INTO @TableName
    WHILE @@FETCH_STATUS = 0
    BEGIN
    DBCC DBREINDEX(@TableName,' ',90)
    FETCH NEXT FROM TableCursor INTO @TableName
    END

    CLOSE TableCursor

    DEALLOCATE TableCursor

    c) by doing this, you will increase overall speed and stability of your database !

    These are basic tips, but very important for you guarantee a great quality and stability of your database, have fun !
     
Thread Status:
Not open for further replies.