Database level lock: current

Showing posts with label current. Show all posts

Thursday, March 29, 2012

database in standby mode

How to find using tsql, that the current database is readonly or in warm
standby mode ?This information is available with DATABASEPROPERTY:
SELECT
DATABASEPROPERTY('MyDatabase', 'IsInStandBy') AS IsInStandBy,
DATABASEPROPERTY('MyDatabase', 'IsReadOnly') AS IsReadOnly
See the DATABASEPROPERTY and DATABASEPROPERTYEX in the Books Online for more
info.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"VSS" <aa@.aa> wrote in message news:OiEGqxUuGHA.1512@.TK2MSFTNGP03.phx.gbl...
> How to find using tsql, that the current database is readonly or in warm
> standby mode ?
>|||You can also use the below command
sp_helpdb <DBNAME>
See the status column...
Thanks
Hari
SQL Server MVP
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:e6vVo5UuGHA.4968@.TK2MSFTNGP03.phx.gbl...
> This information is available with DATABASEPROPERTY:
> SELECT
> DATABASEPROPERTY('MyDatabase', 'IsInStandBy') AS IsInStandBy,
> DATABASEPROPERTY('MyDatabase', 'IsReadOnly') AS IsReadOnly
> See the DATABASEPROPERTY and DATABASEPROPERTYEX in the Books Online for
> more info.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "VSS" <aa@.aa> wrote in message
> news:OiEGqxUuGHA.1512@.TK2MSFTNGP03.phx.gbl...
>> How to find using tsql, that the current database is readonly or in warm
>> standby mode ?
>>
>sql

Thursday, March 22, 2012

database file does not auto grow

Sql 2000 Enterprise SP3
Our current database is 25gb large but grows no longer automatic.
How this are possible?
Properties:
Automaticlly grow file
File growth by percenrtage 10 %
Maximum file size
Unrestricted file growthHi,
YOu have specified 10% auto growth and currently your database is 25 GB.
That means the autogrowth will be >2.5 GB. So just see if you have more than
2.5 GB free space
in hard disk.
Thanks
Hari
"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:F97E1700-B6D7-476E-9D00-0B4E596A5BF8@.microsoft.com...
> Sql 2000 Enterprise SP3
> Our current database is 25gb large but grows no longer automatic.
> How this are possible?
> Properties:
> Automaticlly grow file
> File growth by percenrtage 10 %
> Maximum file size
> Unrestricted file growth
>rowth wi|||Richard
It is possible that the data file has enough free space . How big is your
.BAK file? How long have you noticed that database does not grow?
"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:F97E1700-B6D7-476E-9D00-0B4E596A5BF8@.microsoft.com...
> Sql 2000 Enterprise SP3
> Our current database is 25gb large but grows no longer automatic.
> How this are possible?
> Properties:
> Automaticlly grow file
> File growth by percenrtage 10 %
> Maximum file size
> Unrestricted file growth
>|||49gb free
"Hari Prasad" wrote:
> Hi,
> YOu have specified 10% auto growth and currently your database is 25 GB.
> That means the autogrowth will be >2.5 GB. So just see if you have more than
> 2.5 GB free space
> in hard disk.
>
> Thanks
> Hari
> "Richard" <Richard@.discussions.microsoft.com> wrote in message
> news:F97E1700-B6D7-476E-9D00-0B4E596A5BF8@.microsoft.com...
> > Sql 2000 Enterprise SP3
> > Our current database is 25gb large but grows no longer automatic.
> > How this are possible?
> >
> > Properties:
> > Automaticlly grow file
> > File growth by percenrtage 10 %
> > Maximum file size
> > Unrestricted file growth
> >
> >rowth wi
>
>|||Bak file = 20,3 gb
the problem is about 2 months old
"Uri Dimant" wrote:
> Richard
> It is possible that the data file has enough free space . How big is your
> ..BAK file? How long have you noticed that database does not grow?
>
>
> "Richard" <Richard@.discussions.microsoft.com> wrote in message
> news:F97E1700-B6D7-476E-9D00-0B4E596A5BF8@.microsoft.com...
> > Sql 2000 Enterprise SP3
> > Our current database is 25gb large but grows no longer automatic.
> > How this are possible?
> >
> > Properties:
> > Automaticlly grow file
> > File growth by percenrtage 10 %
> > Maximum file size
> > Unrestricted file growth
> >
> >
>
>|||Richard
> Bak file = 20,3 gb
So , roughly you have 5GB free within data files
> the problem is about 2 months old
I don't think that's problem at all
"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:E6001F5E-CF5C-4E8F-B64D-4EABCB4E8C93@.microsoft.com...
> Bak file = 20,3 gb
> the problem is about 2 months old
> "Uri Dimant" wrote:
>> Richard
>> It is possible that the data file has enough free space . How big is your
>> ..BAK file? How long have you noticed that database does not grow?
>>
>>
>> "Richard" <Richard@.discussions.microsoft.com> wrote in message
>> news:F97E1700-B6D7-476E-9D00-0B4E596A5BF8@.microsoft.com...
>> > Sql 2000 Enterprise SP3
>> > Our current database is 25gb large but grows no longer automatic.
>> > How this are possible?
>> >
>> > Properties:
>> > Automaticlly grow file
>> > File growth by percenrtage 10 %
>> > Maximum file size
>> > Unrestricted file growth
>> >
>> >
>>|||Properties
General tab
space available = 116mb
"Uri Dimant" wrote:
> Richard
> > Bak file = 20,3 gb
> So , roughly you have 5GB free within data files
> > the problem is about 2 months old
> I don't think that's problem at all
>
> "Richard" <Richard@.discussions.microsoft.com> wrote in message
> news:E6001F5E-CF5C-4E8F-B64D-4EABCB4E8C93@.microsoft.com...
> > Bak file = 20,3 gb
> > the problem is about 2 months old
> >
> > "Uri Dimant" wrote:
> >
> >> Richard
> >>
> >> It is possible that the data file has enough free space . How big is your
> >> ..BAK file? How long have you noticed that database does not grow?
> >>
> >>
> >>
> >>
> >> "Richard" <Richard@.discussions.microsoft.com> wrote in message
> >> news:F97E1700-B6D7-476E-9D00-0B4E596A5BF8@.microsoft.com...
> >> > Sql 2000 Enterprise SP3
> >> > Our current database is 25gb large but grows no longer automatic.
> >> > How this are possible?
> >> >
> >> > Properties:
> >> > Automaticlly grow file
> >> > File growth by percenrtage 10 %
> >> > Maximum file size
> >> > Unrestricted file growth
> >> >
> >> >
> >>
> >>
> >>
>
>|||Run
DBCC UPDATEUSAGE (0)
sp_spaceused
"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:B3B31B8F-6561-407E-9D84-DF75038E60DD@.microsoft.com...
> Properties
> General tab
> space available = 116mb
> "Uri Dimant" wrote:
>> Richard
>> > Bak file = 20,3 gb
>> So , roughly you have 5GB free within data files
>> > the problem is about 2 months old
>> I don't think that's problem at all
>>
>> "Richard" <Richard@.discussions.microsoft.com> wrote in message
>> news:E6001F5E-CF5C-4E8F-B64D-4EABCB4E8C93@.microsoft.com...
>> > Bak file = 20,3 gb
>> > the problem is about 2 months old
>> >
>> > "Uri Dimant" wrote:
>> >
>> >> Richard
>> >>
>> >> It is possible that the data file has enough free space . How big is
>> >> your
>> >> ..BAK file? How long have you noticed that database does not grow?
>> >>
>> >>
>> >>
>> >>
>> >> "Richard" <Richard@.discussions.microsoft.com> wrote in message
>> >> news:F97E1700-B6D7-476E-9D00-0B4E596A5BF8@.microsoft.com...
>> >> > Sql 2000 Enterprise SP3
>> >> > Our current database is 25gb large but grows no longer automatic.
>> >> > How this are possible?
>> >> >
>> >> > Properties:
>> >> > Automaticlly grow file
>> >> > File growth by percenrtage 10 %
>> >> > Maximum file size
>> >> > Unrestricted file growth
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>|||sp_spaceused
database_size 22256.56 mb unallocated space -2050.81 mb
reserved 22594752 kb data 17937184 kb index_size 2045344 kb unused 2612224 kb
"Uri Dimant" wrote:
> Run
> DBCC UPDATEUSAGE (0)
> sp_spaceused
>
>
> "Richard" <Richard@.discussions.microsoft.com> wrote in message
> news:B3B31B8F-6561-407E-9D84-DF75038E60DD@.microsoft.com...
> > Properties
> > General tab
> > space available = 116mb
> >
> > "Uri Dimant" wrote:
> >
> >> Richard
> >> > Bak file = 20,3 gb
> >>
> >> So , roughly you have 5GB free within data files
> >>
> >> > the problem is about 2 months old
> >>
> >> I don't think that's problem at all
> >>
> >>
> >> "Richard" <Richard@.discussions.microsoft.com> wrote in message
> >> news:E6001F5E-CF5C-4E8F-B64D-4EABCB4E8C93@.microsoft.com...
> >> > Bak file = 20,3 gb
> >> > the problem is about 2 months old
> >> >
> >> > "Uri Dimant" wrote:
> >> >
> >> >> Richard
> >> >>
> >> >> It is possible that the data file has enough free space . How big is
> >> >> your
> >> >> ..BAK file? How long have you noticed that database does not grow?
> >> >>
> >> >>
> >> >>
> >> >>
> >> >> "Richard" <Richard@.discussions.microsoft.com> wrote in message
> >> >> news:F97E1700-B6D7-476E-9D00-0B4E596A5BF8@.microsoft.com...
> >> >> > Sql 2000 Enterprise SP3
> >> >> > Our current database is 25gb large but grows no longer automatic.
> >> >> > How this are possible?
> >> >> >
> >> >> > Properties:
> >> >> > Automaticlly grow file
> >> >> > File growth by percenrtage 10 %
> >> >> > Maximum file size
> >> >> > Unrestricted file growth
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||10% auto growth is not OK for larger databases. Increment size is 2.5GB in
that case so your disk system would have to write 2.5GB in 30 seconds on
disk. That's almost 100MB/second. Is your disk subsystem that fast? After 30
seconds clients get time-outs (by default) and file growth is canceled. You
can manualy expand file size to desired size or specify smaller fixed
increment size (256MB..)
Tom
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uiEfXEWCHHA.3540@.TK2MSFTNGP03.phx.gbl...
> Run
> DBCC UPDATEUSAGE (0)
> sp_spaceused
>
>
> "Richard" <Richard@.discussions.microsoft.com> wrote in message
> news:B3B31B8F-6561-407E-9D84-DF75038E60DD@.microsoft.com...
>> Properties
>> General tab
>> space available = 116mb
>> "Uri Dimant" wrote:
>> Richard
>> > Bak file = 20,3 gb
>> So , roughly you have 5GB free within data files
>> > the problem is about 2 months old
>> I don't think that's problem at all
>>
>> "Richard" <Richard@.discussions.microsoft.com> wrote in message
>> news:E6001F5E-CF5C-4E8F-B64D-4EABCB4E8C93@.microsoft.com...
>> > Bak file = 20,3 gb
>> > the problem is about 2 months old
>> >
>> > "Uri Dimant" wrote:
>> >
>> >> Richard
>> >>
>> >> It is possible that the data file has enough free space . How big is
>> >> your
>> >> ..BAK file? How long have you noticed that database does not grow?
>> >>
>> >>
>> >>
>> >>
>> >> "Richard" <Richard@.discussions.microsoft.com> wrote in message
>> >> news:F97E1700-B6D7-476E-9D00-0B4E596A5BF8@.microsoft.com...
>> >> > Sql 2000 Enterprise SP3
>> >> > Our current database is 25gb large but grows no longer automatic.
>> >> > How this are possible?
>> >> >
>> >> > Properties:
>> >> > Automaticlly grow file
>> >> > File growth by percenrtage 10 %
>> >> > Maximum file size
>> >> > Unrestricted file growth
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>
>|||Richard
http://www.sql-server-performance.com/database_settings.asp
"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:506BA27E-4C9D-4D37-BD1E-7075074257DA@.microsoft.com...
> sp_spaceused
> database_size 22256.56 mb unallocated space -2050.81 mb
> reserved 22594752 kb data 17937184 kb index_size 2045344 kb unused 2612224
> kb
> "Uri Dimant" wrote:
>> Run
>> DBCC UPDATEUSAGE (0)
>> sp_spaceused
>>
>>
>> "Richard" <Richard@.discussions.microsoft.com> wrote in message
>> news:B3B31B8F-6561-407E-9D84-DF75038E60DD@.microsoft.com...
>> > Properties
>> > General tab
>> > space available = 116mb
>> >
>> > "Uri Dimant" wrote:
>> >
>> >> Richard
>> >> > Bak file = 20,3 gb
>> >>
>> >> So , roughly you have 5GB free within data files
>> >>
>> >> > the problem is about 2 months old
>> >>
>> >> I don't think that's problem at all
>> >>
>> >>
>> >> "Richard" <Richard@.discussions.microsoft.com> wrote in message
>> >> news:E6001F5E-CF5C-4E8F-B64D-4EABCB4E8C93@.microsoft.com...
>> >> > Bak file = 20,3 gb
>> >> > the problem is about 2 months old
>> >> >
>> >> > "Uri Dimant" wrote:
>> >> >
>> >> >> Richard
>> >> >>
>> >> >> It is possible that the data file has enough free space . How big
>> >> >> is
>> >> >> your
>> >> >> ..BAK file? How long have you noticed that database does not
>> >> >> grow?
>> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >> >> "Richard" <Richard@.discussions.microsoft.com> wrote in message
>> >> >> news:F97E1700-B6D7-476E-9D00-0B4E596A5BF8@.microsoft.com...
>> >> >> > Sql 2000 Enterprise SP3
>> >> >> > Our current database is 25gb large but grows no longer automatic.
>> >> >> > How this are possible?
>> >> >> >
>> >> >> > Properties:
>> >> >> > Automaticlly grow file
>> >> >> > File growth by percenrtage 10 %
>> >> >> > Maximum file size
>> >> >> > Unrestricted file growth
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>

database file does not auto grow

Friday, February 24, 2012

Database design for versioned data

We're working on an app that needs to keep versioned data (i.e., the
current values plus all previous values). The versioning is integral
to the app so it's more than just an audit trail or history (some
versioned data needs to link to specific versions of other data).
Can anyone share experiences with the database structure for this type
of requirement or point me to helpful resources?
Thanks,
Sam
----
We're hiring! B-Line Medical is seeking .NET
Developers for exciting positions in medical product
development in MD/DC. Work with a variety of technologies
in a relaxed team environment. See ads on Dice.com.Check out the free book from Richard Snodgrass at:
http://www.cs.arizona.edu/people/rts/publications.html
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Samuel R. Neff" <samuelneff@.nomail.com> wrote in message
news:uh0j73hhdv0po8bbq7cd15nv42inl63v4c@.4ax.com...
> We're working on an app that needs to keep versioned data (i.e., the
> current values plus all previous values). The versioning is integral
> to the app so it's more than just an audit trail or history (some
> versioned data needs to link to specific versions of other data).
> Can anyone share experiences with the database structure for this type
> of requirement or point me to helpful resources?
> Thanks,
> Sam
> ----
> We're hiring! B-Line Medical is seeking .NET
> Developers for exciting positions in medical product
> development in MD/DC. Work with a variety of technologies
> in a relaxed team environment. See ads on Dice.com.|||I have an article on one way to do this, here:
http://simple-talk.com/sql/t-sql-programming/a-primer-on-managing-data-bitemporally/
Note that one of my main references was the book Tibor posted the link to,
so you might want to read that instead of or in addition to this.
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Samuel R. Neff" <samuelneff@.nomail.com> wrote in message
news:uh0j73hhdv0po8bbq7cd15nv42inl63v4c@.4ax.com...
> We're working on an app that needs to keep versioned data (i.e., the
> current values plus all previous values). The versioning is integral
> to the app so it's more than just an audit trail or history (some
> versioned data needs to link to specific versions of other data).
> Can anyone share experiences with the database structure for this type
> of requirement or point me to helpful resources?
> Thanks,
> Sam
> ----
> We're hiring! B-Line Medical is seeking .NET
> Developers for exciting positions in medical product
> development in MD/DC. Work with a variety of technologies
> in a relaxed team environment. See ads on Dice.com.|||Perhaps an app like ApexSQL's Audit can help. Not sure based on your spec
whether you need the data to exist in the exact same tables or not.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Samuel R. Neff" <samuelneff@.nomail.com> wrote in message
news:uh0j73hhdv0po8bbq7cd15nv42inl63v4c@.4ax.com...
> We're working on an app that needs to keep versioned data (i.e., the
> current values plus all previous values). The versioning is integral
> to the app so it's more than just an audit trail or history (some
> versioned data needs to link to specific versions of other data).
> Can anyone share experiences with the database structure for this type
> of requirement or point me to helpful resources?
> Thanks,
> Sam
> ----
> We're hiring! B-Line Medical is seeking .NET
> Developers for exciting positions in medical product
> development in MD/DC. Work with a variety of technologies
> in a relaxed team environment. See ads on Dice.com.

Thursday, March 29, 2012

database in standby mode

Thursday, March 22, 2012

database file does not auto grow

database file does not auto grow

database file does not auto grow

Friday, February 24, 2012

Database design for versioned data

Database level lock

Blog Archive

About Me