-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- Written by Kimberly L. Tripp, SQLskills.com
-- All Rights Reserved.
-- 
-- For more scripts, sample code and additional training resources,
-- go to http://www.SQLskills.com
--	Immerse yourself in SQL Server
--
-- Disclaimer - Thoroughly test this script, execute at your own risk.
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

-- Create the initial version of the database. You *might* need to change pathes in this 
-- script.

CREATE DATABASE [TestFGDB]
	ON PRIMARY 
	(NAME = N'TestFGDBData', 
	FILENAME = N'C:\Program Files\Microsoft SQL Server\mssql.1\mssql\data\TestFGDB2Data.MDF' , 
	SIZE = 10,
	FILEGROWTH = 10%,
	MAXSIZE = 15)
LOG ON 
	(NAME = N'TestFGDBLog', 
	FILENAME = N'C:\Program Files\Microsoft SQL Server\mssql.1\mssql\data\TestFGDBLog.LDF' , 
	SIZE = 10, FILEGROWTH = 10%)
go

-- To set a collation (the is different from the server default) use the COLLATE option 
-- on CREATE DATABASE
--COLLATE collation name

-- To review the list of supported collations in SQL 
--SELECT * FROM ::fn_helpcollations()

-- The filegroup MUST be added FIRST. A file's filegroup choice cannot be changed after
-- the files have been added to the database.
ALTER DATABASE [TestFGDB] ADD FILEGROUP [fg] 
go

-- Adding a file requires the same settings as the file's settings used in create database.
ALTER DATABASE [TestFGDB] 
	ADD FILE
		(NAME = N'TestFGDBFile1', 
		FILENAME = N'C:\Program Files\Microsoft SQL Server\mssql.1\mssql\data\TestFGDBFile1.NDF' , 
		SIZE = 10, FILEGROWTH = 10%) 
		TO FILEGROUP [fg]
go

ALTER DATABASE [TestFGDB] 
	ADD FILE
		(NAME = N'TestFGDBFile2', 
		FILENAME = N'C:\Program Files\Microsoft SQL Server\mssql.1\mssql\data\TestFGDBFile2.NDF' , 
		SIZE = 10, FILEGROWTH = 10%) 
		TO FILEGROUP [fg]
go

ALTER DATABASE [TestFGDB] 
	ADD FILE
		(NAME = N'TestFGDBFile3', 
		FILENAME = N'C:\Program Files\Microsoft SQL Server\mssql.1\mssql\data\TestFGDBFile3.NDF' , 
		SIZE = 10, FILEGROWTH = 10%) 
		TO FILEGROUP [fg]
go

-- Change over the to newly created database and verify the files:
USE TestFGDB
go

sp_helpfile
go

-- Objects created normally (with no specific placement of the data) will go to the "default"
-- filegroup

CREATE TABLE dbo.DemoTableNotOnFilegroup
(
	col1	int	 identity(100,10),
	col2	datetime default current_timestamp,
	col3	datetime default getdate(),
	col4	char(30) default suser_name(),
	col5	char(30) default user_name(),
	col6	char(80) default 'Now is the time for all good men to come to the aid of their country.'
) 
go

-- Use sp_help to verify placement - see the "Data_located_on_filegroup" grid
EXEC sp_help 'dbo.DemoTableNotOnFilegroup'
go

-- TO create an object on a filegroup you need to specify the filegroup in the CREATE statement.
CREATE TABLE dbo.DemoTable2
(
	col1	int	 identity(100,10),
	col2	datetime default current_timestamp,
	col3	datetime default getdate(),
	col4	char(30) default suser_name(),
	col5	char(30) default user_name(),
	col6	char(80) default 'Now is the time for all good men to come to the aid of their country.'
) ON FG
go

-- Use sp_help to verify placement - see the "Data_located_on_filegroup" grid
EXEC sp_help 'dbo.DemoTable2'
go

-- NOTE: this is an infinite loop you will need to manually stop!
-- May want to watch the allocations as shown by Enterprise Manager's taskpad
-- you will see that the files are "proportionally filled" which is NOT
-- the same as striping.
WHILE 1=1
INSERT dbo.DemoTable default values
go

-- To set a user-defined filegroup to be the default use:
ALTER DATABASE TestFGDB
	MODIFY FILEGROUP [fg] DEFAULT
go

-- Setting the READONLY property on a FILEGROUP does NOT yield any performance gains and
-- although it's an option in 2000, I would recommend against explicitly setting it. It's 
-- OK to have filegroups that are used for READ ONLY purposes but do not enforce readonly 
-- with the READONLY filegroup property.
-- Read InstantDoc ID #26067 on www.sqlmag.com for more information.

-- ALTER DATABASE TestFGDB
-- 	MODIFY FILEGROUP [fg] READONLY
go

-- The setting it back to RW is possible too.
-- ALTER DATABASE TestFGDB
-- 	MODIFY FILEGROUP [fg] READWRITE
go

-- Create another filegroup:
ALTER DATABASE [TestFGDB] ADD FILEGROUP [fgtest] 
go

-- Add files to it.
ALTER DATABASE [TestFGDB] 
	ADD FILE
		(NAME = N'TestFGDBTestFile1', 
		FILENAME = N'C:\Program Files\Microsoft SQL Server\mssql\data\TestFGDBTestFile1.NDF' , 
		SIZE = 10, FILEGROWTH = 10%) 
		TO FILEGROUP [fgtest]
go

ALTER DATABASE [TestFGDB] 
	ADD FILE
		(NAME = N'TestFGDBTestFile2', 
		FILENAME = N'C:\Program Files\Microsoft SQL Server\mssql\data\TestFGDBTestFile2.NDF' , 
		SIZE = 10, FILEGROWTH = 10%) 
		TO FILEGROUP [fgtest]
go

-- Create the table's clustered index on the NEW filegroup:
CREATE UNIQUE CLUSTERED INDEX foo 
	ON Demotable(Col1)
	ON fgtest
go

-- Where is the data?
-- Use sp_help to verify placement - see the "Data_located_on_filegroup" grid
EXEC sp_help 'dbo.DemoTable'
go

-- Create a non-clustered index on a different filegroup:
CREATE UNIQUE INDEX bar
	ON Demotable(Col1)
	ON fg
go

-- Review the "located on" portion of the index definition.
EXEC sp_help 'dbo.DemoTable'
go

CREATE TABLE dbo.DemoTable2
(
	col1	int	 identity(100,10),
	col2	datetime default current_timestamp,
	col3	datetime default getdate(),
	col4	char(30) default suser_name(),
	col5	char(30) default user_name(),
	col6	char(80) default 'Now is the time for all good men to come to the aid of their country.'
) -- notice that fg is NOT specified
go
sp_help 'dbo.demotable2'
     -- this will show as filegroup fg because
     -- fg is the DEFAULT filegroup now.
go

-- OK - so let's review what we have...
/* ********************************************************
 Database: TestFGDB
       Consists of 7 files
	TestFGDBData	      PRIMARY
	TestFGDBFile1	      Filegroup = fg, PROPERTY: DEFAULT
	TestFGDBFile2	      Filegroup = fg, PROPERTY: DEFAULT
	TestFGDBFile3	      Filegroup = fg, PROPERTY: DEFAULT
	TestFGDBTestFile1     Filegroup = fgtest, PROPERTY: READWRITE
	TestFGDBTestFile2     Filegroup = fgtest, PROPERTY: READWRITE
	TestFGDBLog	      Transaction Log file
Next - let's try the Shrinking stuff... First let's create the 
scenario where Filegroup fg is too big...right now we have quite 
a bit of space defined BUT not all of it used. We can Shrink and 
empty the TestFGDBFile3 file and then alter and remove it.
******************************************************** */

--Add some data to Demotable2. Make sure to stop this! 
WHILE 1=1
INSERT dbo.DemoTable2 DEFAULT VALUES
go

-- What if we have more files than we need?
ALTER DATABASE TestFGDB
	REMOVE FILE TestFGDBFile3
-- CAN'T remove a file that's not empty.

-- First, need to shrink it with the emptyfile option.
DBCC SHRINKFILE(TestFGDBFile3, EmptyFile)
go

-- Now, you can remove it.
ALTER DATABASE TestFGDB
	REMOVE FILE TestFGDBFile3
go

EXEC sp_helpfile -- And then there were 6 :).
go

-- Now let's try and shrink the entire database so that 
-- there is only 50% free space for each file of the database
DBCC SHRINKDATABASE (TestFGDB, 75)
go

-- Then run 
EXEC sp_helpfile 
-- See the files are smaller...WAIT....NO - they're NOT!
-- DBCC SHRINKDATABASE does not allow you to shrink below
-- the initially created size. Here's a good section from the BOL.
/* **************************************
This is a straight copy from the "Remarks" section of
	DBCC SHRINKDATABASE

The target size for data and log files as calculated by 
DBCC SHRINKDATABASE can never be smaller than the 
minimum size of a file. The minimum size of a file is the 
size specified when the file was originally created, or the 
last explicit size set with a file size changing operation 
such as ALTER DATABASE with the MODIFY FILE option 
or DBCC SHRINKFILE. For example, if all the data and log 
files of mydb were specified to be 10 MB at the time 
CREATE DATABASE was executed, the minimum size of 
each file is 10 MB. DBCC SHRINKDATABASE cannot shrink 
any of the files smaller than 10 MB. If one of the files is 
explicitly grown to a size of 20 MB by using ALTER 
DATABASE with the MODIFY FILE option, the new minimum 
size of the file is 20 MB. To shrink a file to a size smaller 
than its minimum size, use DBCC SHRINKFILE and specify 
the new size. 
**************************************  */
go
-- So can we shrink to a size smaller than the initially created size!
-- Yes...but only file by file with DBCC SHRINKFILE...
-- For simplicity we'll shrink both of the fgtest files to 5 MB each
-- instead of their initially created size of 10MB (each).
DBCC SHRINKFILE(N'TestFGDBTestFile1', 5)
DBCC SHRINKFILE(N'TestFGDBTestFile2', 5)
go

sp_helpfile
go
-- Shrinking the Transaction log... *may* take a little bit more 
-- work than this strategy! But - the steps we use to minimize and
-- clean up log fragmentation will usually solve size issues with 
-- the transaction log. 

-- Check out these sections from the BOL 
-- in this order (so that everything makes sense):
	--Checkpoints and the Active Portion of the Log
	--Shrinking the Transaction Log