收缩数据库.sql

简单的sql语句 数据与汇总。 高手多多指点。

drop database archive
go

select databasepropertyex('master','property')
go
 
exec sp_help 
go

use library
exec sp_spaceused adult
go

CREATE DATABASE Sales
ON PRIMARY
( NAME = SPri1_dat,
   FILENAME = 'c:\program files\microsoft sql server\mssql\data\SPri1dat.mdf',
   SIZE = 10,
   MAXSIZE = 50,
   FILEGROWTH = 15% ),
( NAME = SPri2_dat,
   FILENAME = 'c:\program files\microsoft sql server\mssql\data\SPri2dt.ndf',
   SIZE = 10,
   MAXSIZE = 50,
   FILEGROWTH = 15% ),
FILEGROUP SalesGroup1
( NAME = SGrp1Fi1_dat,
   FILENAME = 'c:\program files\microsoft sql server\mssql\data\SG1Fi1dt.ndf',
   SIZE = 10,
   MAXSIZE = 50,
   FILEGROWTH = 5 ),
( NAME = SGrp1Fi2_dat,
   FILENAME = 'c:\program files\microsoft sql server\mssql\data\SG1Fi2dt.ndf',
   SIZE = 10,
   MAXSIZE = 50,
   FILEGROWTH = 5 ),
FILEGROUP SalesGroup2
( NAME = SGrp2Fi1_dat,
   FILENAME = 'c:\program files\microsoft sql server\mssql\data\SG2Fi1dt.ndf',
   SIZE = 10,
   MAXSIZE = 50,
   FILEGROWTH = 5 ),
( NAME = SGrp2Fi2_dat,
   FILENAME = 'c:\program files\microsoft sql server\mssql\data\SG2Fi2dt.ndf',
   SIZE = 10,
   MAXSIZE = 50,
   FILEGROWTH = 5 )
LOG ON
( NAME = 'Sales_log',
   FILENAME = 'c:\program files\microsoft sql server\mssql\data\salelog.ldf',
   SIZE = 5MB,
   MAXSIZE = 25MB,
   FILEGROWTH = 5MB )
GO

use sales
go
exec sp_helpfilegroup 
go

alter database sales
add filegroup sales3
go

exec sp_helpfilegroup
go

alter database sales
remove filegroup sales3
go

alter database sales
add filegroup salesgroup3
go

exec sp_helpfilegroup 
go

alter database sales
add file
(name = SGrp3Fi1_dat,
   filename = 'c:\program files\microsoft sql server\mssql\data\SGrp3Fi1dt.ndf',
   size= 10,
   maxsize= 50,
   filegrowth = 5 )
to filegroup salesgroup3
go

exec sp_helpfilegroup 
go

exec sp_spaceused  
go   

alter database sales
modify file
(name = SGrp3Fi1_dat,
 size= 15)
go

dbcc shrinkdatabase(sales,25)
go

dbcc shrinkfile(SGrp3Fi1_dat,10)
go