Un-restricted Transaction log file sql 2005 -- sql server new users

sql server new users : Un-restricted Transaction log file sql 2005

Moga
7/17/2006 4:24:38 AM

The below script was used to create a database. The transaction log
file was initially created with restricted growth but was changed to
un-restricted growth.

the issue i have is that the transaction log file reverts to restricted
growth when un-restricted growth is specified.

USE [master]
go
ALTER DATABASE dbname
MODIFY FILE ( NAME = N'dbname_log', MAXSIZE = UNLIMITED, FILEGROWTH =
250MB)

USE [master]
GO
/****** Object: Database [dbname] Script Date: 07/17/2006 12:07:22
******/
CREATE DATABASE [dbname] ON PRIMARY
( NAME = N'dbname', FILENAME = N'F:\MSSQL\Data\dbname.mdf' , SIZE =
3072000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 307200KB )
LOG ON
( NAME = N'dbname_log', FILENAME = N'E:\MSSQL\Log\dbname_log.ldf' ,
SIZE = 2561024KB , MAXSIZE = 2048GB , FILEGROWTH = 256000KB )
COLLATE Latin1_General_CI_AS
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'dbname', @new_cmptlevel=90
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [dbname].[dbo].[sp_fulltext_database] @action = 'disable'
end
GO
ALTER DATABASE [dbname] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [dbname] SET ANSI_NULLS OFF
GO
ALTER DATABASE [dbname] SET ANSI_PADDING OFF
GO
ALTER DATABASE [dbname] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [dbname] SET ARITHABORT OFF
GO
ALTER DATABASE [dbname] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [dbname] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [dbname] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [dbname] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [dbname] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [dbname] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [dbname] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [dbname] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [dbname] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [dbname] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [dbname] SET ENABLE_BROKER
GO
ALTER DATABASE [dbname] SET AUTO_UPDATE_STATISTICS_ASYNC ON
GO
ALTER DATABASE [dbname] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [dbname] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [dbname] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [dbname] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [dbname] SET READ_WRITE
GO
ALTER DATABASE [dbname] SET RECOVERY FULL
GO
ALTER DATABASE [dbname] SET MULTI_USER
GO
ALTER DATABASE [dbname] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [dbname] SET DB_CHAINING OFF

ALTER DATABASE dbname
MODIFY FILE ( NAME = N'dbname_log', MAXSIZE = UNLIMITED, FILEGROWTH =
250MB)

Moga
7/17/2006 6:35:44 AM

[quoted text, click to view]

Thank for your response

Below is the output of sp_helpdb 'dbname'

dbname 5501.00 MB sa 20 Jul 17 2006 Status=3DONLINE,
Updateability=3DREAD_WRITE, UserAccess=3DMULTI_USER, Recovery=3DFULL,
Version=3D611, Collation=3DLatin1_General_CI_AS, SQLSortOrder=3D0,

Steen Persson (DK)
7/17/2006 2:26:32 PM

[quoted text, click to view]
Hi

How do you see it's not unlimited? The maximum logfile size in SQL2005
is 2TB and that's the max logfiles size you see when you run sp_helpdb.
This indicates that it is set to unlimited filegrowth - even though it's
not very clear....:-).

--
Regards
Steen Schlüter Persson

Steen Persson (DK)
7/18/2006 12:00:00 AM

[quoted text, click to view]
....and here you can see that the max file size for your log file is
2147483648 which is 2TB. I assume this is the way the sp reports that
the log file is set to unlimited growth.

--
Regards
Steen Schlüter Persson

Moga
7/18/2006 12:55:54 AM

[quoted text, click to view]

hi Steen

The script below is used to restrict the growth of the log file. After
the script has executed it reverts to un-restricted growth.

ALTER DATABASE dbname
MODIFY FILE ( NAME =3D N'dbname_log', MAXSIZE =3D UNLIMITED, FILEGROWTH =3D
250MB)

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server new users

home · blog · groups

Questions? Comments? Contact the dn