Monday, May 9, 2011

Calculating a Different Date

I'm sure we've all had a need to calculate dates.  Basically, taking the current date and doing some math on it, in order to derive another date.  For example, the first day of the month or week or quarter, the last day of this month or last, or even the last day of this year.  In this tip I will just show you a quick way to perform this type of calculation, using DATEADD, DATEPART and DATEDIFF:

DECLARE @today DATETIME
DECLARE @datefirst TINYINT
SET @today = CONVERT(CHAR(8), GETDATE(), 112) 
SET @datefirst = @@DATEFIRST
SET DATEFIRST 7

SELECT DATEADD(DAY, -(DATEPART(WEEKDAY, @today)) + 1, @today) [1st Day of This Week]
SELECT DATEADD(DAY, -(DATEPART(WEEKDAY, @today)) + 7, @today) [Last Day of This Week]
SELECT DATEADD(DAY, -DATEPART(DAY, @today) + 1, @today) [1st Day of This Month]
SELECT DATEADD(qq, DATEDIFF(qq,0,@today), 0) [1st Day of This Qtr]
SELECT DATEADD(DAY, -DATEPART(DAY, @today), DATEADD(MONTH, 1, @today)) [Last Day of This Month]
SELECT DATEADD(MONTH, -1, DATEADD(DAY, - DAY(@Today) + 1, @Today)) [1st Day of Last Month]
SELECT DATEADD(DAY, - DAY(@Today), @Today) [Last Day of Last Month]
SELECT DATEADD(DAY, -DATEPART(DAYOFYEAR, @today) + 1, @today) [1st Day of This Year]
SELECT CAST(CAST(YEAR(@today) AS CHAR(4)) + '1231' AS DATETIME) [Last Day of This Year]

SET DATEFIRST @datefirst

The above is all very dynamic, based on @today.  This example will actually show you how to count the number of days, weekdays or even the hours, between two given dates, @StartDate and @EndDate:

DECLARE @StartDate DATETIME = '1/1/2011',
        @EndDate DATETIME = GETDATE()


/* Calculate # of Days between two dates */
SELECT DATEDIFF(dd,@StartDate,@EndDate) [Total # Days]

/* Calculate # of Weekdays between same two dates */
SELECT DATEDIFF(d,@StartDate,@EndDate)+1
- (DATEDIFF(wk,@StartDate,@EndDate) + CASE WHEN DATEPART(dw,@StartDate)=1 then 1 else 0 End )
- (DATEDIFF(wk,@StartDate,@EndDate) + CASE WHEN DATEPART(dw,@EndDate)=7 then 1 else 0 End ) [Total # Weekdays]

/* Calculate # of Hours between same two dates */
SELECT DATEDIFF(second,@startdate,@EndDate)/60.0/60.0 [Total # Hours]

Sunday, May 8, 2011

sys.database_permissions

This is just a very quick script to review your permissions at the database level, for each principal. Remember, a 'principal' is an entity that can utilize SQL Server resources, like any one of the following:

  SQL user
  Windows user
  Windows group
  Application role
  Database role
  User mapped to a certificate
  User mapped to an asymmetric key

This logic is just a quick way to return privileges defined for each of the above principals:

     WITH permsCTE as
      (
     SELECT
        USER_NAME(p.grantee_principal_id) principal_name,
        dp.principal_id,
        dp.type_desc AS principal_type_desc,
        p.class_desc,
        OBJECT_NAME(p.major_id) AS object_name,
        p.permission_name,
        p.state_desc AS permission_state_desc
     FROM 
        sys.database_permissions p INNER JOIN sys.database_principals dp
          ON p.grantee_principal_id = dp.principal_id   )

     --users
    SELECT
      p.principal_name,  p.principal_type_desc, p.class_desc,
      p.[object_name],p.permission_name,p.permission_state_desc,
      CAST(NULL AS SYSNAME) [role_name]
    FROM 
      permsCTE p
    WHERE 
      principal_type_desc <> 'DATABASE_ROLE'

         UNION

     --role members
    SELECT
      rm.member_principal_name,rm.principal_type_desc,p.class_desc, 
      p.object_name,p.permission_name,p.permission_state_desc,
      rm.role_name
    FROM permsCTE p RIGHT OUTER JOIN (
       SELECT 
         role_principal_id, 
         dp.type_desc [principal_type_desc], 
         member_principal_id,
         user_name(member_principal_id)  
         [member_principal_name],
         user_name(role_principal_id) 
         [role_name]
        FROM
            sys.database_role_members rm INNER JOIN sys.database_principals dp
              ON rm.member_principal_id = dp.principal_id ) rm
      ON rm.role_principal_id = p.principal_id
     ORDER BY 1



Here's a screenshot of the output from one of my instances:











See here for more information on sys.database_permissions and sys.database_principals:

  https://msdn.microsoft.com/en-us/library/ms188367.aspx
  https://msdn.microsoft.com/en-us/library/ms187328.aspx

Friday, May 6, 2011

sys.sql_logins, Password Expiration

Here's a good one:


   Logon Error: 18487, Severity: 14, State: 1. 
  Logon Login failed for user 'loginname'. Reason: The password of the account has   
  expired. [CLIENT: <named pipe>]


Your company's web application is running, but... the password has expired for the SQL login.  The 'Enforce password policy' and 'Enforce password expiration' configurations were introduced in v2005.  Review your SQL login properties, and be certain that you know if either of these policies are being enforced.  Not only that, I encourage you to check the varied statistics on your logins;  # of failed login attempts, last failed login, how many days until X login's password expires, which logins are locked out, etc. :

  SELECT
     name [SQL Login],
     CASE WHEN is_policy_checked = 0 THEN 'Disabled'
        WHEN is_policy_checked = 1 THEN 'Enabled' END 
     [Enforce Password Policy],
     CASE WHEN is_expiration_checked = 0 THEN 'Disabled' 
        WHEN is_expiration_checked = 1 THEN 'Enabled' END   
     [Enforce Password Expiration],
     CASE LOGINPROPERTY(name, 'IsLocked')
        WHEN 0 THEN 'No'
        WHEN 1 THEN 'Yes'
        ELSE 'Unknown' END as IsLocked,
     LOGINPROPERTY(name, 'PasswordLastSetTime') 
     [PasswordSetDate],
     LOGINPROPERTY(name, 'BadPasswordCount') 
     [FailedLoginAttempts],
     LOGINPROPERTY(name, 'BadPasswordTime') [LastFailedLogin],
     LOGINPROPERTY(name, 'LockoutTime') [LockedOutDate],
     CASE LOGINPROPERTY(name, 'IsExpired')
        WHEN 0 THEN 'Password is not expired'
        WHEN 1 THEN 'Password is not expired, change it'
        ELSE 'Unknown' END [PasswordExpired],
     CASE LOGINPROPERTY(name, 'IsMustChange')
        WHEN 0 THEN 'Must not change password at next login'
        WHEN 1 THEN 'Must change password at next login'
        ELSE 'Unknown' END [PasswordChangeOnNextLogin],
     LOGINPROPERTY(name, 'DaysUntilExpiration')    
     [DaysUntilPasswordExpires]
   FROM 
     sys.sql_logins
   WHERE 
     [name] NOT LIKE '##%'
   ORDER BY 
     [name]



Sysadmin & SecurityAdmin Fixed Server Roles

Did you know that in v2005/v2008, a member of the securityadmin fixed server role can grant CONTROL SERVER permissions?  That is far too close to sysadmin for my comfort.  Not only that, but they can add themselves to the SA role at anytime, to do anything.  I always check security on servers that I have inherited, so that I know who has securityadmin or sysadmin on each instance. This is just a quick check on those two fixed server roles:

  SELECT 
     l.name, 
     l.denylogin, 
     l.isntname, 
     l.isntgroup, 
     l.isntuser
  FROM 
     master.sys.syslogins l
  WHERE 
     l.sysadmin = 1 OR l.securityadmin = 1
  ORDER BY 
     l.isntgroup, 
     l.isntname, 
     l.isntuser


At the absolute minimum, make sure that you know about everybody returned in that list.  You might even try logging in as each one of them, with a blank password.  Please also take a look at this article by Brian Kelley, on CONTROL SERVER: Restricting SecurityAdmin on SQL Server 2005/2008.

Restore Database Backup From Another Server

On one of my recent projects, I needed to restore a backup from production to another development server, on a daily basis.  I used the logic below in a SQL Server Agent job on the dev server, to identify the remote backup, and restore it locally.  

Of course, the two servers have to be linked, but first I connect to the production server, and query the msdb database in order to retrieve the remote backup filename as @backupfile.  In this case, the format of the filename is like this:

     C:\MSSQL\Backup\DatabaseName_DB_20110122.bak

I wrote this into a BakFiles working table, so that I could manipulate it for the actual restore.  Because I was restoring from the remote server, I needed to update the filename to include the UNC path, like this:

    \\ProductionServerName\C$\MSSQL\Backup\DatabaseName_DB_20110122.bak

At that point, the local database is then set to SINGLE_USER in order to perform the restore, and set back to MULTI_USER afterward.  Pretty much like this:



  SET NOCOUNT ON;
  
  DECLARE @backupfile VARCHAR(100)
  SET @backupfile = (
   SELECT TOP (1) BUMF.physical_device_name
   FROM ProductionServerName.msdb.dbo.backupmediafamily AS BUMF 
   INNER JOIN ProductionServerName.msdb.dbo.backupmediaset AS BUMS   
    ON BUMF.media_set_id = BUMS.media_set_id 
   INNER JOIN ProductionServerName.msdb.dbo.backupfile AS BUF 
   INNER JOIN ProductionServerName.msdb.dbo.backupset AS BUS 
    ON BUF.backup_set_id = BUS.backup_set_id 
    ON BUMS.media_set_id = BUS.media_set_id
   WHERE (BUS.database_name = 'DatabaseName')
   AND (BUMF.physical_device_name LIKE  
    'C:\MSSQL\Backup\DatabaseName\DatabaseName_db_%') 
   ORDER BY BUS.backup_start_date DESC 
   )


  /* BAKFILES WORKING TABLE */
  TRUNCATE TABLE dbo.BakFiles
  INSERT dbo.BakFiles (bakfilename)
  SELECT (@backupfile)


    UPDATE dbo.BakFiles
  SET bakfilename =   
  REPLACE(bakfilename,'C:\','\\ProductionServerName\C$\')


   DECLARE @newfile VARCHAR(100)
  SELECT @newfile = [bakfilename] FROM dbo.BakFiles 


  /* SET DATABASE TO SINGLE_USER */
  ALTER DATABASE DatabaseName 
  SET SINGLE_USER WITH ROLLBACK IMMEDIATE


  /* RESTORE DATABASE */
  RESTORE DATABASE DatabaseName
  FROM DISK = @newfile
  WITH REPLACE, STATS = 5,
  MOVE 'DatabaseName_Data.mdf' TO   
   'D:\MSSQL10.MSSQLSERVER\MSSQL\Data\DatabaseName_Data.mdf',
  MOVE 'DatabaseName_Log.ldf' TO 
   'D:\MSSQL10.MSSQLSERVER\MSSQL\Log\DatabaseName_Log.ldf'
  GO


  /* SET BACK TO MULTI_USER */
  ALTER DATABASE DatabaseName
  SET MULTI_USER


  SET NOCOUNT OFF;
  
I also ran another step after this, to drop and recreate the database users, and then add them back to the appropriate role.  You'll need to replace 'ProductionServerName' and 'DatabaseName' to suit your needs, but that's pretty much it.  Providing your remote server is reachable, and the backup files are available, it should work just fine.  


TRUNCATE_ONLY in SQL 2008

BACKUP LOG WITH TRUNCATE_ONLY is no longer supported in SQL v2008.  In previous versions, this command would empty your transaction logs, without backing them up.  Unfortunately, if you truncate your log, you lose the ability to recover beyond a certain point in time.  I'm guessing that is probably one of the reasons Microsoft removed the function from v2008.

There is a method to be used in v2008, but in my opinion, your recovery is still jeopardized.  If you MUST do it, then you need to alter the database recovery model to SIMPLE, in order to empty the transaction log.  You can then run DBCC SHRINKFILE to recover disk space, and then switch your recovery model back to FULL.  See here:


   /* TRUNCATE LOG BY CHANGING RECOVERY MODEL */
   ALTER DATABASE YourDatabase 
   SET RECOVERY SIMPLE;
   GO

   /* SHRINK THE DATABASE LOG FILE TO DESIRED SIZE */
   DBCC SHRINKFILE ('YourDatabase_Log' , 25) ; -- <<CHANGE AS NEEDED

   GO


   /* CHANGE THE RECOVERY MODEL BACK TO FULL */
   ALTER DATABASE YourDatabase 
   SET RECOVERY FULL;
   GO


Please understand, if your database is in SIMPLE recovery mode, the transaction log is automatically truncated.  This is only necessary if you are not using SIMPLE recovery mode.

See this list of other functions that are no longer available in v2008:   Discontinued Functionality

Thursday, May 5, 2011

Move Large Table to New Filegroup

I started a new contract recently, and I found one of the databases to be fairly large in size, sitting on one drive... with about 20MB remaining, and very quickly running out of space.  Fortunately, there were other drives on the server with adequate space, so I decided to move one of the largest tables onto a different drive.  Here is a quick reference for you to see how to move an existing table to a new filegroup:

  /* ADD FILEGROUP */
  ALTER DATABASE DatabaseName
  ADD FILEGROUP NewFileGroupName
  GO

  /* ADD FILE  */
  ALTER DATABASE DatabaseName
  ADD FILE 
  ( NAME = NewFileName,
    FILENAME = 'I:\MSSQL\DATA\NewFileName.ndf',
    SIZE = 50, 
    FILEGROWTH = 1MB)
    TO FILEGROUP NewFileGroupName
   GO

Now that we've got our new filegroup and file, we can move an existing table to it by rebuilding the table's Primary Key.  First we need to run the drop constraint statement using the MOVE TO option, in order to move the table to the new filegroup.  Then, we can re-create the constraint.  See here:


  ALTER TABLE TableName
  DROP CONSTRAINT ConstraintName WITH (MOVE TO NewFileGroupName)
  GO

  ALTER TABLE TableName 
  ADD CONSTRAINT ConstraintName PRIMARY KEY(KeyName)
  GO


That's basically it.  You can run this to see that the table is now located on the new filegroup:

  EXEC sp_help TableName



Here is another nice query to let you see which objects reside upon each of your filegroups:  

Check your filegroups.





CURSOR through tables & columns

I recently started a new contract, and inherited a new set of databases.  The firm never really had a DBA in place, so I needed to evaluate the overall object (and data) design.  Many, many moons ago, I developed a procedure for this type of assessment.  I analyze the table definition, and the data within each table, in order to confirm whether the table design actually suits the data being stored.

First, we will look at the min, max and average data lengths of the existing data, in comparison to the actual column datalength definition.  See here:

/*  GETS MIN,MAX,AVG DATALENGTHS OF ALL COLUMNS IN A GIVEN TABLE.  */
DECLARE @table SYSNAME
SET @table = 'customer'
SET NOCOUNT ON;

DECLARE @i INT
DECLARE @cnt INT
DECLARE @str VARCHAR(8000)

DECLARE @column_name VARCHAR(50)
SET @i=0
SELECT IDENTITY(SMALLINT,1,1) AS col_num, @table TableName, column_name,null [MIN],NULL [MAX], NULL [AVG],data_type,character_maximum_length into #t 
FROM information_schema.columns 
WHERE table_name=@table 

SELECT @cnt=count(column_name) FROM #t
WHILE @i< @cnt+1
BEGIN
 SELECT @column_name=column_name FROM #t WHERE col_num=@i
 SET @str = 'UPDATE #t  
 SET [MIN]=a.[MIN],
  [MAX]=a.[MAX],
  [AVG]=a.[AVG] 
 FROM 
(SELECT min(len(isnull(' + @column_name+ ',0))) [MIN] , 
MAX(len(isnull(' + @column_name+ ',0))) [MAX],AVG(len(isnull(' + @column_name+ ',0)))  [AVG] from ' + @table + ' ) a  , #t b  where b.col_num='+ cast(@i as varchar)

 EXEC (@str)
 SET @i=@i+1
END

SELECT * FROM #t

SET NOCOUNT OFF;

DROP TABLE #t

In the above, I am reviewing the 'customer' table.  I have returned only a few of the attributes, which you will see were created all of the same data type and length (yuck):

col_num   tablename     column_name    min     max     avg     datatype  character_maximum_length
1 customer   MerchantID             5        5       5 varchar 255
2 customer              MerchStoreID         5      11       6 varchar 255
3 customer              CustID                     7       8        7 varchar 255
4 customer              CustSSN                 6       9        8 varchar 255
5 customer              CustName              8      38      14 varchar 255

As you will see, the min, max and avg lengths are returned, and you can see your VARCHAR(255) datalengths are just a bit extreme.  Even more, take a look at that MerchantID --- all values are a datalength of 5.  Wouldn't this be better typed as CHAR(5)?  The overhead of the variable datatype is just not worth it here, where all values are of the same datalength.

Basically, I use the above method to assess the current table definition, and the data within.  Another step that is necessary is to check for NULL or blank values.  I often find attributes where all values are NULL.  In that case, why keep the column?  If you don't use it, get rid of it.  Unused attributes are not free -- the physical data storage as well as the data manipulation around the NULLs.  I use this method to cursor through each table column, returning counts on any records that are found to be NULL:

SET NOCOUNT ON;


USE MyDatabase;
DECLARE @tbl VARCHAR(35),@col VARCHAR(1000),@sql NVARCHAR(4000)


DECLARE Cur1 CURSOR FAST_FORWARD FOR
SELECT o.name tbl, c.name col
FROM sys.sysobjects o INNER JOIN sys.syscolumns c ON o.id = c.id
WHERE o.xtype='U'
ORDER BY o.name


OPEN Cur1
FETCH NEXT FROM Cur1 INTO @tbl, @col
WHILE @@fetch_status = 0
BEGIN


SET @sql = '
IF(SELECT COUNT(*) FROM dbo.'+@tbl+' WITH (NOLOCK) WHERE '+@col+' IS NULL) > 0
SELECT COUNT(*) AS '''+@tbl+','+@col+''' FROM dbo.'+@tbl+' WITH (NOLOCK) WHERE '+@col+' IS NULL
'


   PRINT @sql
-- EXECUTE sp_executesql @sql --UNCOMMENT TO ACTUALLY RUN
FETCH NEXT FROM Cur1 INTO @tbl, @col
END
CLOSE Cur1
DEALLOCATE Cur1


SET NOCOUNT OFF;


The way it is written, you will print the output, rather than actually execute it, like this:

  IF(SELECT COUNT(*) FROM dbo.company WHERE companyID IS NULL) > 0
  SELECT COUNT(*) AS 'company,companyID' FROM dbo.company WHERE companyID IS NULL

  IF(SELECT COUNT(*) FROM dbo.company WHERE name IS NULL) > 0
  SELECT COUNT(*) AS 'company,name' FROM dbo.company WHERE name IS NULL

You can either copy/paste that into another window, to run it, or you can uncomment the EXEC statement, and execute again.  I encourage you to try it out in the dev bed, or after hours.  Let me know if you have any questions.

Remove duplicates with a CTE

'CTE' means Common Table Expression.  Per BOL, this is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, MERGE, or DELETE statement. The CTE is a new feature enabled in v2005 and forward, that can be used in stored procedures, as recursive queries, even for removing duplicates!  In this example, I will show you how to remove duplicates from a table WITHOUT a primary key:

USE MyDatabase;

IF OBJECT_ID('MyTable','u')>0
DROP TABLE dbo.MyTable
GO
CREATE TABLE MyTable (
 Col1 VARCHAR(10),
 Col2 VARCHAR(10)
);

/* Insert data with dupes */
 INSERT INTO MyTable(Col1,Col2)
 VALUES ('Jack','Jill');
 INSERT INTO MyTable(Col1,Col2)
 VALUES ('Jack','Jill');
 INSERT INTO MyTable(Col1,Col2)
 VALUES ('Jack','Jill');
 INSERT INTO MyTable(Col1, Col2)  
 VALUES ('WhenHarry','MetSally');
 INSERT INTO MyTable(Col1,Col2)
 VALUES ('WhenHarry','MetSally');
 INSERT INTO MyTable(Col1,Col2)
 VALUES ('WhenHarry','MetSally');
 INSERT INTO MyTable(Col1,Col2)
 VALUES ('WhenHarry','MetSally');
 INSERT INTO MyTable(Col1,Col2)
 VALUES ('Peter','Paul'); 

/* Quick look at your data */
 SELECT * FROM MyTable;

Col1              Col2
Jack                   Jill
Jack                   Jill
Jack                   Jill
WhenHarry     MetSally
WhenHarry     MetSally
WhenHarry    MetSally
WhenHarry    MetSally
Peter              Paul

/* Remove dupes */
 DECLARE @Col1 VARCHAR(10),
   @Col2 VARCHAR(10),
 @PreviousCol1 VARCHAR(10),
 @PreviousCol2 VARCHAR(10);

  WITH CTE AS(
  SELECT 
COALESCE(Col1,'') AS Col1, 
COALESCE(Col2,'') AS Col2, ROW_NUMBER() OVER(PARTITION BY col1,col2 ORDER BY col1,col2) AS row_id
  FROM MyTable
  )

DELETE CTE WHERE row_id >1;

/* See that your dupes are gone. */
SELECT * FROM MyTable 

    Col1              Col2
    Jack               Jill
    WhenHarry    MetSally
    Peter              Paul


Tuesday, May 3, 2011

TCP Port & IP Address

Very quick method to confirm the TCP Port that SQL Server is listening on:

  DECLARE @tcp_port NVARCHAR(5)
  EXEC xp_regread
     @rootkey     =     'HKEY_LOCAL_MACHINE',
     @key    =    'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP',
     @value_name    =    'TcpPort',
     @value        =    @tcp_port OUTPUT

  SELECT @tcp_port [Port]

This can also be done through the SQL Server Configuration Manager.  See 'SQL Server Network Configuration' in the left pane, and then select 'Protocols for <INSTANCENAME>'.   Double-click the 'TCP/IP' protocol name in the right pane, and this will open a 'TCP/IP Properties' dialog.  Select the 'IP Addresses' tab, and you will see the TCP Port.  See here:



You can also use xp_cmdshell to return the IP Address of the SQL Server you are connected to, like this:

  EXEC master.dbo.xp_cmdshell 'ipconfig'

That will return all of the other media and state details specfic to the network, such as DNS, Subnet Mask, Default Gateway, etc.  Try this if you ONLY want to return the SQL Server IP Address:

  CREATE TABLE #ipconfig(
   captured_line VARCHAR(255)
  )
  INSERT #ipconfig
  EXECUTE xp_cmdshell 'ipconfig /all';

  SELECT 
    LTRIM(RTRIM(CAST(PARSENAME(SUBSTRING(captured_line,40,15),4) AS VARCHAR(4))))+'.'+
    LTRIM(RTRIM(CAST(PARSENAME(SUBSTRING(captured_line,40,15),3) AS VARCHAR(3))))+'.'+
    LTRIM(RTRIM(CAST(PARSENAME(SUBSTRING(captured_line,40,15),2) AS VARCHAR(3))))+'.'+
    LTRIM(RTRIM(CAST(PARSENAME(SUBSTRING(captured_line,40,15),1) AS VARCHAR(3)))) [IP Address]
  FROM 
    #ipconfig
  WHERE 
    captured_line like '%IPv4 Address%';

  DROP TABLE #ipconfig

Sunday, May 1, 2011

sys.dm_os_memory_clerks

The sys.dm_os_memory_clerks is a very useful DMV for monitoring SQL Server memory performance.  You could use sys.dm_os_memory_clerks to identify exactly where SQL's memory is going.  Basically, which SQL components are using exactly how much memory.  See here:


SELECT 
   type,
   sum(virtual_memory_reserved_kb) [VM Reserved],
   sum(virtual_memory_committed_kb) [VM Committed],
   sum(awe_allocated_kb) [AWE Allocated],
   sum(shared_memory_reserved_kb) [SM Reserved], 
   sum(shared_memory_committed_kb) [SM Committed],
   sum(multi_pages_kb) [MultiPage Allocator],
   sum(single_pages_kb) [SinlgePage Allocator]
FROM 
   sys.dm_os_memory_clerks 
GROUP BY 
   type
ORDER BY 
   8 desc

Filegroup Manipulations

I've got a few posts up here with different filegroup manipulations, but here is a very quick reference for adding/removing files and filegroups to your database:

  /* ADD FILEGROUPS */
  ALTER DATABASE DatabaseName
  ADD FILEGROUP FilegroupName
  GO

  /* ADD FILES  */
  ALTER DATABASE DatabaseName
  ADD FILE 
  ( NAME = Filename,
 FILENAME = 'C:\MSSQL\DATA\Filename.ndf',SIZE = 50, FILEGROWTH = 10%)
  TO FILEGROUP FilegroupName

  /* REMOVE FILES  */
  ALTER DATABASE DatabaseName
  REMOVE FILE Filename
  GO

  /*  REMOVE FILEGROUPS  */
  ALTER DATABASE DatabaseName
  REMOVE FILEGROUP FilegroupName
  GO 



A couple others...