* Pre-allocate the log to appropriate size
* Minimize auto growths
* Log might NOT show 0 Percent Log Used after backup for example - the % Log Used will be ~6%
* Disk Bound Systems might experience some performance degradation at log backup
* Consider RAID 1+0 instead of RAID 1, RAID 5 generally is not recommended
* Optimize existing file – minimize VLFs
- - -
source : SQL Server Community In touch
* Inherit initial properties from model database
* Change to model will affect ALL databases created AFTER the change made.
Note : TempDb is re-Created on each system restart. Might be a good idea for data types that are needed for temp table/objects
* Define logical file names
* Define physical location
* Define auto growth setting
* Create DataBase [junkDb] on Primary
(Name = N'JunkDbData', FileName = N'Physical FileName',
Size = 10, --kb, mb, gb, tb
FileGrowth = 10%, --kb, mb, gb, tb
MaxSize = 15) --kb, mb, gb, tb
Log on
(Name=N'JunkDbLog',
FileName = N'Physical FileName',
Size = 10, --kb, mb, gb, tb
FileGrowth = 10%, --kb, mb, gb, tb
MaxSize = unlimited)
* Default for filegrowth is 10%
* Default for MAXSIZE is Unlimited
If you need an example of creating a table for application versioning, maybe this script will help :
CREATE TABLE [dbo].[dnn_Version](
[VersionId] [int] IDENTITY(1,1) NOT NULL,
[Major] [int] NOT NULL,
[Minor] [int] NOT NULL,
[Build] [int] NOT NULL,
[CreatedDate] [datetime] NOT NULL,
CONSTRAINT [PK_dnn_Version] PRIMARY KEY CLUSTERED
(
[VersionId] ASC
) ON [PRIMARY],
CONSTRAINT [IX_dnn_Version] UNIQUE NONCLUSTERED
(
[Major] ASC,
[Minor] ASC,
[Build] ASC
) ON [PRIMARY]
) ON [PRIMARY]
Note : This script taken from dotnetnuke database
This is the sql script to get other SP resultset in our store procedure:
CREATE PROC PROC_X
AS
CREATE TABLE #TmpTable( ... )
INSERT INTO #TmpTable EXEC usp_MySP @1, @2
Sql script:
select
convert(varchar,
dateadd(d,
cast(right('2007265',3) as int)-1,
cast(left('2007265',4)+'0101' as datetime)
) ,
112) as the_date
