deAttach database: files

Showing posts with label files. Show all posts

Thursday, March 29, 2012

Deadlocks problem when database files growing

We are running a very busy SQL Server 2000 Enterprise in the cluster "passive active" environment. Hundreds of transactions are going through and all of them are
logged in to a special database we've created. For an each real transaction we are getting around 10 records inserted in to this database.
I found that whenever the database grows its files, especially the log file, we're getting a lot of deadlocks which we are able to resolve only by failing over to another node.

Any suggestions would be appreciated.

Thanks,

DanHowdy

Is this a recent problem of a long term issue?
Sounds like an application design issue....I doubt he growing logfiles would cause the problem - they would just be a symptom of how busy the system is. Also, if the logs grow really quickly, its possible the app is holding open tables etc too long and then causing the deadlocks. Shorter tansactuions may help. I have used locking hint TABLOCKX to get around a lot of problems, but it MAY NOT be the best solution for you. Sounds very application specific.........

I assume you have plenty of disk space for the TEMPDB and the database files?

More info / background would be useful.

Cheers,

SG

Wednesday, March 7, 2012

DDF Files to SQL

Is there a way to import data form Peachtree DDF files into SQL server
directly or does the information need to be parsed or filtered thru something
like Crystal Reports to have the information exported to a Database?
If you can find an ODBC driver or OLE DB provider for your PeachTree DDFs,
then you can use DTS to load those files into SQL Server.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Chris Chandler" <see@.top.com> wrote in message
news:Xns9596727A4D0C1nospmnet@.207.46.248.16...
Is there a way to import data form Peachtree DDF files into SQL server
directly or does the information need to be parsed or filtered thru
something
like Crystal Reports to have the information exported to a Database?
|||"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in news:
#ooS7VcwEHA.3832@.TK2MSFTNGP10.phx.gbl:

> If you can find an ODBC driver or OLE DB provider for your PeachTree DDFs,
> then you can use DTS to load those files into SQL Server.
Been Googling for one for a while

DDF Files to SQL

Is there a way to import data form Peachtree DDF files into SQL server
directly or does the information need to be parsed or filtered thru somethin
g
like Crystal Reports to have the information exported to a Database?If you can find an ODBC driver or OLE DB provider for your PeachTree DDFs,
then you can use DTS to load those files into SQL Server.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Chris Chandler" <see@.top.com> wrote in message
news:Xns9596727A4D0C1nospmnet@.207.46.248.16...
Is there a way to import data form Peachtree DDF files into SQL server
directly or does the information need to be parsed or filtered thru
something
like Crystal Reports to have the information exported to a Database?|||"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in news:
#ooS7VcwEHA.3832@.TK2MSFTNGP10.phx.gbl:

> If you can find an ODBC driver or OLE DB provider for your PeachTree DDFs,
> then you can use DTS to load those files into SQL Server.
Been Googling for one for a while

DDF Files to SQL

Is there a way to import data form Peachtree DDF files into SQL server
directly or does the information need to be parsed or filtered thru something
like Crystal Reports to have the information exported to a Database?If you can find an ODBC driver or OLE DB provider for your PeachTree DDFs,
then you can use DTS to load those files into SQL Server.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Chris Chandler" <see@.top.com> wrote in message
news:Xns9596727A4D0C1nospmnet@.207.46.248.16...
Is there a way to import data form Peachtree DDF files into SQL server
directly or does the information need to be parsed or filtered thru
something
like Crystal Reports to have the information exported to a Database?|||"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in news:
#ooS7VcwEHA.3832@.TK2MSFTNGP10.phx.gbl:
> If you can find an ODBC driver or OLE DB provider for your PeachTree DDFs,
> then you can use DTS to load those files into SQL Server.
Been Googling for one for a while

Saturday, February 25, 2012

DBs, sizes etc

Anyone here with a ready to go sqlscript that lists all db's, files, sizes, owner etc? I guess it's a combination of sp_databases, sp_helpdb and sp_helpdb [db].There are scripts out there that will do this for you.

Be aware of how they report db sizings etc.|||www.sqlservercentral.com has all kinds of them. Registration is free. You can go to the scripts area and download to your heart's content.

DBREINDEX failed with DB ONLINE and filegroup read-only

Hello everybody,

I have a very stranger problem that I need to understand...

I have one DB with 3 files and 2 filegroups (primary and FGTESTE). After to place FGTESTE filegroup as read-only, DBCC CHECKDB (DBTESTE3) failed with error:

Msg 5030, Level 16, State 12, Line 1
The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.

I noticed that if I kill all connections of the database DBCC work fine, but if a have any connections on DB, DBCC failed.

Some idea of the why DBCC do not work with database online?

Steps to Reproduce

1. Open new query (conn1) and create new database
CREATE DATABASE DBTESTE3
GO
-- Add new filegroup
ALTER DATABASE DBTESTE3 ADD FILEGROUP FGTESTE
GO
-- Add file to new filegroup
ALTER DATABASE DBTESTE3 ADD FILE (NAME=DBTESTE3_Data2, FILENAME='C:\DBTESTE3_Data2.ndf')
TO FILEGROUP FGTESTE
GO
-- Alter filegroup to readonly
ALTER DATABASE DBTESTE3 MODIFY FILEGROUP FGTESTE READONLY
GO
2. Run DBCC in conn1
-- Here DBCC run OK
DBCC CHECKDB (DBTESTE3)
3. Open new query window (conn2) and set database as DBTESTE3. This open a connection to DBTESTE3.
4. Go to conn1 and run DBCC again
-- Now I get Dbcc error
DBCC CHECKDB (DBTESTE3)

Hello Storage Team...

Please, Is this a normal issue ?

Nilton Pinheiro
SQL Server MVP

|||

This should work.

A couple of questions:

What version/SP of SQL are you using?

Does this scenario work if you do not set the filegroup to readonly?

|||

Hi Kevin....thanks for you help !!

Well, I have Windows Server 2003 Standard x64 SP1 + SQL 2005 Enterprise SP1 (I have machine with Windows Enterprise 2003 x64 or x32 with SQL 2005 SP1 and problem is show too).

This is my SELECT @.@.version output

Microsoft SQL Server 2005 - 9.00.2047.00 (X64)
Apr 14 2006 01:11:53
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)

This is my sp_helpfile after create DB:

DBTESTE3..sp_helpfile
DBTESTE3 1 E:\MSSQL.1\MSSQL\DATA\DBTESTE3.mdf
DBTESTE3_log 2 E:\MSSQL.1\MSSQL\DATA\DBTESTE3_log.LDF
DBTESTE3_Data2 3 E:\DBTESTE3_Data2.ndf

Where E:\ is a NTFS file ssytem.

Does this scenario work if you do not set the filegroup to readonly? Yes !!

thanks
Nilton Pinheiro

|||

I have reproduced this as well. It appears to be a bug, and I have filed it as such.

We will be working to get a fix for this out as soon as we can.

|||

very good Kevin...thanks for you help.

Nilton Pinheiro
SQL Server MVP

|||

Hello Kevin,

Do you have some information about this bug? Does SP2 fix it?

Thanks
Nilton Pinheiro
www.mcdbabrasil.com.br

|||

This turned out to be a design limitation that was not documented. We hope to address this in the next release of SQL Server and will document the limitation in the meantime.

There is a workaround of creating a database snapshot and running the DBCC CHECKDB against the snapshot for those Editions that support database snapshots.

|||

Hi Peter, thanks for attention and feedback.

I think that a KB would be very good :)

Thanks
Nilton Pinheiro
www.mcdbabrasil.com.br

|||It is my understanding that there is one in the works.

Sunday, February 19, 2012

dbo rolemembership and files size of database files

Hi
If I create a database and give to the database user
dbo role membership he is able to change size of datafiles
mdf and ldf. How can i suppress that so that this is no longer
possible?
Kind regards
MickeyDBO should not have this right by default. Make sure the user is also not
a member of
sysadmin or diskadmin fixed server roles.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Tuesday, February 14, 2012

DB-LIB and SQL 2005

Hello,

I've an application developped in VC ++ 6.0 using Db library for bulk copying flat files records to a SQL server (SQL server 7) table. We're trying to migrate to MSSQL 2005. While performing tests, the bcp command fails telling that "The primary key constraint has been violated'. All other Db lib command works with no problem. I've even changed the format file (.fmt) in eliminating the Primary key field as well as in the flat file (source file). But the bcp_exec command succeeds with no problem if i delete the primary constraint. Where's the problem with this new version of SQL SERVER 2005 ?

Thanks for your help

Let me see if I understand you correctly.

You have an application that copies a flat file into a SQL Server 2005 table using bcp_exec in the DB-Lib. When trying to do this, you receive the error "The primary key constraint has been violated". If you delete the primary key constraint on the table, bcp_exec then succeeds. Is this correct? Did the table in SQL Server 7 contain the primary key constraint as well but imports worked anyways?

You probably know this, but I'll state it anyways -- primary keys must be unique, including that they cannot be set to NULL. If I understand correctly, simply deleting the columns from the flat file and the format file will not work as that would try to put a default value of NULL in the primary key column, something not allowed.

Does the table in SQL Server 7 already have unique values in this column? If so, I'm surprised that there would be a problem, unless there's a collision with the data already in the table on SQL Server 2005.

I would suggest comparing the schemas. If they are identical, including constraints, then see if there is something amiss in the data, a NULL or something else. If there isn't, then try looking at what's already in the SQL Server 2005 table and compare that to what's in the flat file. Is there a conflict or collision there preventing the import?

If none of these is the case, please reply and we can see what else we can find.

|||

Thanks a lot for your reply.

The sql server 7.0 table contains the primary key constraint and the bcp_exec via DB-Lib works fine. The format file references this primary key and in the flat file that column contains 0 for each record. The field separator is ;. The table was created on SQLSERVER 2005 using the same commands as for SQL 7.

CREATE TABLE [dbo].[Table1] (
[Field1] [int] IDENTITY (1, 1) NOT NULL ,
etc
etc...
)
GO

ALTER TABLE [dbo].[Table1] WITH NOCHECK ADD
PRIMARY KEY CLUSTERED
(
[Field1]
) ON [PRIMARY]
GO

As i've explained earlier that when i found that the bcp_exec did'nt work with SQL 2005, i've complety removed the primary key (field1 in our ex.) from the format file and the Zros from the flat file. Nothing doing... this time bcp exec fails with no message.

Thanks a lot again

DBfile usage

Hi,
I have a database that is currently 8Gb. I run some tools (like Log PI) and
tells me that the usage of my files is 25%, around 2Gb. The rest is free.
How can I truncate or shrink the files/tables whatever? INDEXDEFRAG and DBCC
SHRINKDATABASE or DBCC SHRINKFILE will not work. Needless to say that the
GUI maintenance palns and jobs are almost useless. Any help?
regardsHi,
Use the below command to get the actual free space:-
For Data and Index
use dbname
go
sp_spaceused @.updateusage='true'
For Transaction log
DBCC SQLPERF(LOGSPACE)
Based on the outcome you can shrink the MDF and LDF file seperately.
Steps:-
1. Backup the transaction log (Backup Log in books online)
2. Make the database single user
Alter database <dbname> set single_user with rollback immediate
3. Now shrink the files
dbcc shrinkfile('logical_mdf_name',size)
4. Shrink the LDF file
dbcc shrinkfile('logical_ldf_name',size)
5. After this check the size again
use dbname
go
sp_spaceused @.updateusage='true'
For Transaction log
DBCC SQLPERF(LOGSPACE)
6. Make the database multi user
Alter database <dbname> set multi_user
--
Thanks
Hari
MCDBA
"dimitris" <dimitris@.microsoft.com> wrote in message
news:eEuNfDKZEHA.1048@.tk2msftngp13.phx.gbl...
> Hi,
> I have a database that is currently 8Gb. I run some tools (like Log PI)
and
> tells me that the usage of my files is 25%, around 2Gb. The rest is free.
> How can I truncate or shrink the files/tables whatever? INDEXDEFRAG and
DBCC
> SHRINKDATABASE or DBCC SHRINKFILE will not work. Needless to say that the
> GUI maintenance palns and jobs are almost useless. Any help?
> regards
>|||thanks Harri,
this is how my db looks like. Should I proceed with your recommendations?
DB_Name Db_size unallocated space
Axapta 9753.19 MB 1142.38 MB
reserved data index size unused
7521080 KB 2809752 KB 4593024 KB 118304 KB
and the log space is:
logsize log space used
Axapta 1265.9922 0.87000221 0
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eStdWmKZEHA.2260@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Use the below command to get the actual free space:-
> For Data and Index
> use dbname
> go
> sp_spaceused @.updateusage='true'
> For Transaction log
> DBCC SQLPERF(LOGSPACE)
> Based on the outcome you can shrink the MDF and LDF file seperately.
> Steps:-
> 1. Backup the transaction log (Backup Log in books online)
> 2. Make the database single user
> Alter database <dbname> set single_user with rollback immediate
> 3. Now shrink the files
> dbcc shrinkfile('logical_mdf_name',size)
> 4. Shrink the LDF file
> dbcc shrinkfile('logical_ldf_name',size)
> 5. After this check the size again
> use dbname
> go
> sp_spaceused @.updateusage='true'
> For Transaction log
> DBCC SQLPERF(LOGSPACE)
> 6. Make the database multi user
>
> Alter database <dbname> set multi_user
> --
> Thanks
> Hari
> MCDBA
>
> "dimitris" <dimitris@.microsoft.com> wrote in message
> news:eEuNfDKZEHA.1048@.tk2msftngp13.phx.gbl...
> > Hi,
> > I have a database that is currently 8Gb. I run some tools (like Log PI)
> and
> > tells me that the usage of my files is 25%, around 2Gb. The rest is
free.
> > How can I truncate or shrink the files/tables whatever? INDEXDEFRAG and
> DBCC
> > SHRINKDATABASE or DBCC SHRINKFILE will not work. Needless to say that
the
> > GUI maintenance palns and jobs are almost useless. Any help?
> >
> > regards
> >
> >
>|||Hi,
Yes go ahead. Take a full database backup before doing the activity.
Command to backup"-
Backup database <dbname> to disk='d:\backup\dbname.bak' with init
--
Thanks
Hari
MCDBA
"dimitris" <dimitris@.microsoft.com> wrote in message
news:#Ela4ELZEHA.2408@.tk2msftngp13.phx.gbl...
> thanks Harri,
> this is how my db looks like. Should I proceed with your recommendations?
> DB_Name Db_size unallocated space
> Axapta 9753.19 MB 1142.38 MB
> reserved data index size unused
> 7521080 KB 2809752 KB 4593024 KB 118304 KB
> and the log space is:
> logsize log space used
> Axapta 1265.9922 0.87000221 0
>
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eStdWmKZEHA.2260@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> >
> > Use the below command to get the actual free space:-
> >
> > For Data and Index
> >
> > use dbname
> > go
> > sp_spaceused @.updateusage='true'
> >
> > For Transaction log
> >
> > DBCC SQLPERF(LOGSPACE)
> >
> > Based on the outcome you can shrink the MDF and LDF file seperately.
> >
> > Steps:-
> >
> > 1. Backup the transaction log (Backup Log in books online)
> > 2. Make the database single user
> >
> > Alter database <dbname> set single_user with rollback immediate
> >
> > 3. Now shrink the files
> >
> > dbcc shrinkfile('logical_mdf_name',size)
> >
> > 4. Shrink the LDF file
> >
> > dbcc shrinkfile('logical_ldf_name',size)
> >
> > 5. After this check the size again
> >
> > use dbname
> > go
> > sp_spaceused @.updateusage='true'
> >
> > For Transaction log
> >
> > DBCC SQLPERF(LOGSPACE)
> >
> > 6. Make the database multi user
> >
> >
> > Alter database <dbname> set multi_user
> >
> > --
> > Thanks
> > Hari
> > MCDBA
> >
> >
> > "dimitris" <dimitris@.microsoft.com> wrote in message
> > news:eEuNfDKZEHA.1048@.tk2msftngp13.phx.gbl...
> > > Hi,
> > > I have a database that is currently 8Gb. I run some tools (like Log
PI)
> > and
> > > tells me that the usage of my files is 25%, around 2Gb. The rest is
> free.
> > > How can I truncate or shrink the files/tables whatever? INDEXDEFRAG
and
> > DBCC
> > > SHRINKDATABASE or DBCC SHRINKFILE will not work. Needless to say that
> the
> > > GUI maintenance palns and jobs are almost useless. Any help?
> > >
> > > regards
> > >
> > >
> >
> >
>

Dbf To Ms Sql Server 2000

:eek: HI ALL
I m having my data in DBF file format. now i want to use my .Dbf files Data in to SQL Server 2000. so i can use SQL query on that and extract data from that. i want to use my DBF data in SQL. tell me how it is possible. plz give me soloution ...
Thanx
Manu VermaDo you still have a database server that will read the dbf files? If so, you can connect to it from SQL Server using DTS and import the data.|||Do you still have a database server that will read the dbf files? If so, you can connect to it from SQL Server using DTS and import the data.

we can read DBF files even in Excel or in VFoxpro. tell me the way to Import the data. i tried in SQL Server 2000. I select the Visual Foxpro ODBC Driver it gave the error. How can i import data via using DTS?|||we can read DBF files even in Excel or in VFoxpro. tell me the way to Import the data. i tried in SQL Server 2000. I select the Visual Foxpro ODBC Driver it gave the error. How can i import data via using DTS?

Follow this...
Open EM>Right click on Database>All Tasks>Import data>Select Microsoft Visual Foxpro Driver as data source>Select User/SystemDSN>New>System data source>Visual foxpro driver>Give data source name>Browse the database>Select the created DSN> and you are through...:cool:|||I select the Visual Foxpro ODBC Driver it gave the error.The error? Believe it or not, there are actually several errors you can get. Care to post the message you received?|||Hi,

You might need to download the latest drivers for Foxpro/Dbf.
Thanks

RN|||When i import the data from Foxpro .dbf file to sql, there is error:

Insert error, Column 47 ('mrlvbd',DBType_DBTimeStamp),status 6: Data Overflow.

Then i found out that it might be because of the date field of the data is blank. What can i do to solve this?

Thanks in advance|||I have found the solution.

1 ) In the Select Source Dialog Box,select the Transform Button.

2) Change the Destination Datatype of the related field from SmallDateTime to DateTime

Thanks

Thursday, March 29, 2012

Deadlocks problem when database files growing

Wednesday, March 7, 2012

DDF Files to SQL

DDF Files to SQL

DDF Files to SQL

Saturday, February 25, 2012

DBs, sizes etc

DBREINDEX failed with DB ONLINE and filegroup read-only

Sunday, February 19, 2012

dbo rolemembership and files size of database files

Tuesday, February 14, 2012

DB-LIB and SQL 2005

DBfile usage

DBfile usage

DBfile usage

Dbf To Ms Sql Server 2000

deAttach database

Blog Archive

About Me