چگونه اسکریپت‌های بزرگ را بدون خطای حافظه در SSMS اجرا کنیم!؟

چگونه اسکریپت‌های بزرگ را بدون خطای حافظه در SSMS اجرا کنیم!؟

نوشته شده توسط: سید محمد حسینی
تاریخ انتشار: ۲۴ بهمن ۱۳۹۴
آخرین بروزرسانی: ۲۶ اردیبهشت ۱۴۰۲
زمان مطالعه: 6 دقیقه
۵
(۱)

مقدمه

آیا تا به حال با این موقعیت مواجه شده اید که بخواهید یک اسکریپت بزرگ را در SSMS اجرا کنید و با این پیغام خطا مواجه شوید؟

“Cannot execute script. Insufficient memory to continue the execution of the program. (mscorlib)”
 
این پیغام خطا به این علت است که SSMS نمی تواند فایل های اسکریپت بزرگ را مدیریت کند و این مشکل حتی در SQL Server 2014 نیز باقی مانده است. در ادامه قصد داریم به جای استفاده از SSMS، این مشکل را با استفاده از ابزار SQLCMD و در خط فرمان رفع کنیم.
برای شروع ما نیاز به یک فایل اسکریپت  T-SQL بزرگ جهت اجرا در SSMS داریم تا مشکل تشریح شده را مشاهده کنیم
در آغاز یک بانک اطلاعاتی TestDB  و یک جدول TestTable ایجاد می کنیم:
 USE master
GO
CREATE DATABASE TestDB
GO
USE TestDB
GO
CREATE TABLE TestTable
(
ID INT,
Value INT
)
GO
حال باید فایل اسکریپت T-SQL را جهت درج اطلاعات درون جدول TestTable، در SSMS اجرا کنیم. در این تمرین، فایل اسکریپت مورد استفاده ما دارای حجم ۸۴MB جهت درج ۱۵۰۰۰۰۰ رکورد در جدول می باشد(جهت ایجاد این فایل اسکریپت می توان از کد زیر استفاده نمود)
DECLARE @i int=1
WHILE @i<=1500000
BEGIN
PRINT 'INSERT INTO TestTable (ID, Value) VALUES('+CAST(@i AS VARCHAR)+','+CAST(@i+@i AS VARCHAR)+')'
IF @i%1000=0
PRINT 'GO'
SET @i+=1
END
GO
پس از اجرای این اسکریپت، با خطای زیر مواجه خواهیم شد:
همانطور که گفته شد، این شرایط در مواقعی رخ می دهد که حجم فایل اسکریپت برای SSMS بزرگ بوده و SSMS قادر به مدیریت این فایل نباشد.

استفاده از SQLCMD جهت اجرای اسکریپت های T-SQL بزرگ

برای اجرای اسکریپت های T-SQL بزرگ، می توان به جای SSMS، ابزار SQLCMD را مورد استفاده قرار داد تا از طریق خط فرمان قادر به اجرای این اسکریت ها باشیم. SQLCMD ابزاریست که امکان اجرای دستورات T-SQL، فایل های اسکریپت و همچنین رویه های ذخیره شده(Stored Procedures) را از طریق خط فرمان فراهم می کند.
البته باید توجه داشت که SSMS از طریق Microsoft .NET Framework SqlClient دستورات را اجرا می کند، ولی SQLCMD در خط فرمان و از طریق ODBC دستورات را اجرا می کند.
فرض کنید که نام سرور و اینستنس ما HOSEINI-PC\MSSQL2014 باشد، همچنین نام و مسیر فایل اسکریپت مورد نظر D:\InsertData.sql باشد. جهت مشخص کردن فایل ورودی و اجرای آن بر روی اینستنس مورد نظر، از دستورات زیر در خط فرمان استفاده می شود.
Sqlcmd –S HOSEINI-PC\MSSQL2014  –d TestDB  –i “D:\InsertData.sql”
 
با فشار دادن کلید Enter، این اسکریپت شروع به اجرا شده و پس از اجرا، پیغام اجرای موفقیت آمیز اسکریپت قابل مشاهده است.
 
همچنین می توان در SSMS رکوردهای ثبت شده را مورد بررسی قرار داد
 SP_SPACEUSED '[dbo].[TestTable]'

در پایان

SSMS برای اجرای فایل های اسکریپت بزرگ قابل استفاده نیست. در هر حال درمواردی ما نیاز به مدیریت فایل های بزرگ(نظیر بارگذاری حجم زیادی از اطلاعات، اجرای اسکریپت های ایجاد بانک های اطلاعاتی که از روی بانک های اطلاعاتی بزرگ تولید شده اند و…) داریم و با استفاده از ابزار SQLCMD از طریق خط فرمان، امکان رفع این مشکل را خواهیم داشت.

چه رتبه ای می‌دهید؟

میانگین ۵ / ۵. از مجموع ۱

اولین نفر باش

title sign
معرفی نویسنده
سید محمد حسینی
مقالات
11 مقاله توسط این نویسنده
محصولات
0 دوره توسط این نویسنده
سید محمد حسینی
پروفایل نویسنده
title sign
دیدگاه کاربران

    • سلام وقت بخیر
      لطفا راهنمایی کنید چرا با اینکه بر روی سرور ۱۶ گیگ رم دارم و در تسک منیج ویندوز هم ۸ گیگ بیشتر مصرف نشده (۵۰% رم ) ولی اسکیوال ۲۰۱۹ در اجرای دستورات out of memory می دهد .
      البته این موضوع زمانی بروز می کند که تقاضاهای روی دیتابیس افزایش پیدا می کند .
      در این حالت اگر مستقیما به سرور ریموت زده و وارد استودیو منیج هم بشویم باز قادر به اجرا دستورات نیست و تنها راه ریست سرور یا ریست iis است .
      ۱- چرا اسکیوال از کل حافظه موجود بر روی سرور استفاده نمی کند ؟
      ۲- چرا پس از اتمام یک تراکنش ، رم مصرف شده را آزاد نمی کند ؟

      مشخصات سرور :
      ویندوز سرور ۲۰۱۹
      اسکیوال ۲۰۱۹
      بکند Asp.net Web form
      روش اتصال به دیتابیس Ado.net

      با سپاس

      • درود بر شما
        به نقل از مهندس مسعود طاهری

        تنظیمات max memory و همچنین تعداد سی پی یو در دسترس برای SQL server را چک کنید
        و مورد مهم این است که شما باد زحمت بکشید کویری های که هزینه اجرای آنها بالا است استخراج کرده و Tune کنید

        تشکر از همراهی شما

        • سلام مجدد
          از پاسخ شما متشکرم
          من قبلاً دوره اسکیوال برای برنامه نویسان رو خریداری کردم .
          لطفا راهنمایی کنید مواردی که فرمودید در همین سرفصل آموزشی هست ؟

          • درود بر شما
            لطفا با اکانت وب مستر در تلگرام در ارتباط باشید تا سرفصل هارو دراختبار شما قرار بدند.

            @nikamoozwebmaster

            تشکر از همراهی شما

    •    بسیار عالی بود . امروز واقعا بهم کمک کرد .
      ممنون از همه دوستان

    •   سلام 

      ممنون از لطفتون
      استاد طاهری راه کار دیگری معرفی فرمودند
      البته طبق گفته خود استاد، در برخی از سیستم ها که دارای Workload بالایی هستند، بهتره که ما با SSMS زیاد کار نکنیم
      شاید این روش برای این چنین سیستم هایی مناسب باشد
      • سلام محمد جان

        روش شما خوب است.
        نکته ی که فر مودید درست است کار کردن با SSMS و انجام تنظیمات حساس با ویزارد معمولا روی سرور عملیات توصیه نمیشه و بهتر کارها با اسکریپت انجام بشه.
        ———
        یک نکته دیگه هم که می تونه مفید باشه 
        اگر بخواهیم حجم زیادی دیتا درج کنیم بهتر است از روش Export دیتا و درج دیتا در مقصد به شکل Bulk استفاده کنیم . 
        استفاده از SSIS
        استفاده از ویزارد Import Export
        و…
    • سلام

      ضمن تشکر از شما بابت مطلب خوبی که ارائه دادید.
      بنده به شخصه با چنین موضوعی تا بحال برخورد نکرده بودم و پس از مرور مطالب میخواستم بپرسم آیا راهکار دیگه ای به غیر از SQLCMD وجود داره یا نه؟ که دیدم آقای طاهری عزیز به لینک خوبی برای برخورد با این مورد اشاره کردند. (ممنون)
    •    با سلام و خسته نباشید خدمت شما

      من اسکریپت مورد نظر را اجرا گردم اما اصلا به من ارور نداد و به درستی ایجاد کرد
      من بر روی sql server 2008 r2 تست کردم

      با تشکر از شما

    •     سلام

      با تشکر از مقاله عالی که ارائه کردین
    •     جناب حسینی روشی که معرفی کردید خوب بود. 

      برای اینکه بتوانیم اینکار را در Management Studio انجام دهیم می توانیم تنظیمات زیر را انجام دهیم (تنظیم Maximum Script Size در SSMS)
    •     جناب حسینی روشی که معرفی کردید خوب بود. 

      برای اینکه بتوانیم اینکار را در Management Studio انجام دهیم می توانیم تنظیمات زیر را انجام دهیم (تنظیم Maximum Script Size در SSMS)
      •     سلام 

        ممنون از توجه و راهنمایی شما
    •    سلام
      تشکر از مقاله خوبتون عالی بود

    •     ممکن است تنظیمات  (تنظیم Maximum Script Size در SSMS فعال بوده باشد.

هر روز یک ایمیل، هر روز یک درس
آموزش SQL Server بصورت رایگان
همین حالا فرم زیر را تکمیل کنید
دانلود رایگان جلسه اول
نیک آموز علاوه بر آموزش، پروژه‌های بزرگ در حوزه هوش تجاری و دیتا انجام می‌دهد.
close-link